﻿using System;
using System.Collections;
using System.Web;
using System.IO;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.Globalization;
using System.Data.OleDb;
using System.Data.Odbc;

/// <summary>
/// Summary description for DB
/// </summary>
public class DB
{
	public DB()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    private static SqlConnection OpenConnection()
    {
        SqlConnection conn = null;

        try
        {
            conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=RRHHDB;Integrated Security=True;Pooling=False");
            conn.Open();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        return conn;
    }

    // ************************************************** Delete's **************************************************************************
    // **                                                                                                                                  **
    // **                                               Espacio para Delete's                                                              **
    // **                                                                                                                                  **
    // **                                                                                                                                  **
    // ************************************************** Delete's **************************************************************************

    public static int DeleteCompanyAccount(int id)
    {

        //borra la cuenta que se le solicita


        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_company_bank where id=@bank";
            comm.Parameters.Add("@bank", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;

    }

    public static int DeleteContactInfo(int id)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_persona_direccion where tpd_id=@tpd_id";
            comm.Parameters.Add("@tpd_id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int DeleteConpania(int id)
    {
        int result = 0;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            comm.CommandText = " delete from tbl_compania where tco_id=@id ";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
            comm.Parameters.Clear();

            comm.CommandText = " delete from tbl_vista_documento  where tco_id=@id ";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
            comm.Parameters.Clear();

            comm.CommandText = " delete from tbl_compania_documento where compania_id=@id ";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
            comm.Parameters.Clear();

            comm.CommandText = " delete from tbl_company_bank where idcompany=@id ";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
            comm.Parameters.Clear();

            comm.CommandText = " delete from tbl_cuenta where tcu_tco_id=@id ";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
            comm.Parameters.Clear();

            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static int DeleteDocumento(int id)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_empleado_documento where id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            comm.ExecuteNonQuery();
            result = 1;
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int DeleteDocumentoCom(int id)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = " delete from tbl_compania_documento where id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            comm.ExecuteNonQuery();
            result = 1;
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int DeleteLocation(int id)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_localidad where loc_id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static int DeleteEmergencyContact(int id)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_persona_emergencia where tpem_id=@tpem_id";
            comm.Parameters.Add("@tpem_id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int DeleteCuenta(int cuentaid)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_cuenta where tcu_id=@tcu_id";
            comm.Parameters.Add("@tcu_id", SqlDbType.Int).Value = cuentaid;
            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int DeleteJobPosition(int id)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_puesto_empleado where tpe_id=@tpe_id";
            comm.Parameters.Add("@tpe_id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int DeleteJobEmployement(int id)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_tipo_contrato where ttc_id=@ttc_id";
            comm.Parameters.Add("@ttc_id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int DeleteExpedienteInfo(int id)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_expediente where tex_id=@tex_id";
            comm.Parameters.Add("@tex_id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int DeletePersona(int id)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_persona where tper_id=@tper_id";
            comm.Parameters.Add("@tper_id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static int DeleteEmployee(int id)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_empleado where emp_id=@emp_id";
            comm.Parameters.Add("@emp_id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int DeleteEmpleadoCuentaBanco(int id) {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_empleado_accbank where emp_accbank_id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    
    
    }

    public static int DeleteBank(int id)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_banco where id=@id";// busca el valor id en la tabla 
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id; // 
            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;

    }

    public static int DeleteDoc(int id)
    {

        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from tbl_tipo_documento where id=@id";// busca el valor id en la tabla 
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id; // 
            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;


    }



    // ************************************************** select's **************************************************************************
    // **                                                                                                                                  **
    // **                                               Espacio para Select's                                                              **
    // **                                                                                                                                  **
    // **                                                                                                                                  **
    // ************************************************** select's **************************************************************************

    public static int getCountAccount(int compid) {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select COALESCE(count(*),0) from tbl_cuenta where tcu_tco_id=@tcu_tco_id";
            comm.Parameters.Add("@tcu_tco_id", SqlDbType.Int).Value = compid;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) result = reader.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int getCompanybyAccount(int accid)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tcu_tco_id from tbl_cuenta where tcu_id=@tcu_id";
            comm.Parameters.Add("@tcu_id", SqlDbType.Int).Value = accid;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) result = reader.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int getCountEmployee(int compid)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select COALESCE(count(*),0) from tbl_empleado where emp_tcu_id in (select tcu_id from tbl_cuenta where tcu_tco_id=@tcu_tco_id)";
            comm.Parameters.Add("@tcu_tco_id", SqlDbType.Int).Value = compid;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) result = reader.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int getCountEmployeebyCuenta(int cuentaid)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select COALESCE(count(*),0) from tbl_empleado where emp_tcu_id=@emp_tcu_id";
            comm.Parameters.Add("@emp_tcu_id", SqlDbType.Int).Value = cuentaid;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) result = reader.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    
    public static int getIDEmpleadoCuenta(int per_id, int cue_id)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select emp_id from tbl_empleado where emp_tper_id=@emp_tper_id and emp_tcu_id=@emp_tcu_id";
            comm.Parameters.Add("@emp_tper_id", SqlDbType.Int).Value = per_id;
            comm.Parameters.Add("@emp_tcu_id", SqlDbType.Int).Value = cue_id;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) result = reader.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getArrTipoAnotacion(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tan_id, tan_tco_id, tan_nombre, tan_descripcion from tbl_tipo_anotacion "+where;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id anotacion
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1);//compania
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//nombre
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//descripcion
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getArrPuestoEmpleado(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tpe_id, tpe_tco_id, tpe_nombre, tpe_descripcion, tpe_salario_minimo, tpe_salario_maximo, tpe_currency, tpe_aumento from tbl_puesto_empleado" + where + " order by tpe_nombre";
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1);//compania
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//nombre
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//descripcion
                if (!reader.IsDBNull(4)) rgb.decC1 = reader.GetDecimal(4);//Salario minimo
                if (!reader.IsDBNull(5)) rgb.decC2 = reader.GetDecimal(5);//salario maximo
                if (!reader.IsDBNull(6)) rgb.strC3 = reader.GetString(6);//moneda
                if (!reader.IsDBNull(7)) rgb.decC3 = reader.GetDecimal(7);//aumento
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getArrCuenta(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tcu_id, tcu_tco_id, tcu_nombre, tcu_descripcion, tcu_manager, tcu_extraorder from tbl_cuenta " + where;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1);//compania
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//nombre
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//descripcion
                if (!reader.IsDBNull(4)) rgb.strC3 = reader.GetString(4);//manager
                if (!reader.IsDBNull(5)) rgb.decC1 = reader.GetDecimal(5);//manager
                rgb.intC3=  DB.getCountEmployeebyCuenta(rgb.intC1);
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getArrLocation(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select loc_id, loc_tco_id, loc_name, loc_addr1, loc_addr2, loc_city, loc_country from tbl_localidad " + where;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1);//compania
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//nombre
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//direccion
                if (!reader.IsDBNull(4)) rgb.strC3 = reader.GetString(4);//direccion2 
                if (!reader.IsDBNull(5)) rgb.strC4 = reader.GetString(5);//ciudad
                if (!reader.IsDBNull(6)) rgb.strC5 = reader.GetString(6);//pais
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }


    public static ArrayList getArrTipoContrato(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select ttc_id, ttc_tco_id, ttc_nombre, ttc_descripcion from tbl_tipo_contrato " + where;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1);//compania
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//nombre
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//descripcion
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
   
    public static ArrayList getArrCompania(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tco_id, tco_nombre, tco_nit, tco_direccion1, tco_direccion2, tco_ciudad, tco_state, tco_zip, tco_country,";
            comm.CommandText += " tco_telefono1, tco_telefono2, tco_telefono3, tco_fax, tco_website, tco_contact_email,tbl_country.name from tbl_compania INNER JOIN  tbl_country ON tbl_compania.tco_country = tbl_country.id " + where;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//nit
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);//direccion1
                if (!reader.IsDBNull(4)) rgb.strC4 = reader.GetString(4);//direccion2
                if (!reader.IsDBNull(5)) rgb.strC5 = reader.GetString(5);//ciudad
                if (!reader.IsDBNull(6)) rgb.strC6 = reader.GetString(6);//state
                if (!reader.IsDBNull(7)) rgb.strC7 = reader.GetString(7);//zip
                if (!reader.IsDBNull(8)) rgb.strC8 = reader.GetInt32(8).ToString();//country
                if (!reader.IsDBNull(9)) rgb.strC9 = reader.GetString(9);//tel1
                if (!reader.IsDBNull(10)) rgb.strC10 = reader.GetString(10);//tel2
                if (!reader.IsDBNull(11)) rgb.strC11 = reader.GetString(11);//tel3
                if (!reader.IsDBNull(12)) rgb.strC12 = reader.GetString(12);//fax
                if (!reader.IsDBNull(13)) rgb.strC13 = reader.GetString(13);//www
                if (!reader.IsDBNull(14)) rgb.strC14 = reader.GetString(14);//email
                if (!reader.IsDBNull(15)) rgb.strC15 = reader.GetString(15);//countryname
                rgb.intC2 = DB.getCountAccount(rgb.intC1);//numero de cuentas
                rgb.intC3 = DB.getCountEmployee(rgb.intC1);//numero de empleados
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString()+e.StackTrace+ comm.CommandText);
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getContactArr(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tpd_id, tpd_direccion1, tpd_direccion2, tpd_ciudad, tpd_state, tpd_zip, tpd_country, tpd_telefono1, tpd_telefono2, tpd_telefono3, tpd_work_email, tpd_personal_email, tpd_tper_id ";
            comm.CommandText += " from tbl_persona_direccion " + where;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//direccion1
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//direccion2
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);//ciudad
                if (!reader.IsDBNull(4)) rgb.strC4 = reader.GetString(4);//state
                if (!reader.IsDBNull(5)) rgb.strC5 = reader.GetString(5);//zip
                if (!reader.IsDBNull(6)) rgb.strC6 = reader.GetString(6);//country
                if (!reader.IsDBNull(7)) rgb.strC7 = reader.GetString(7);//tel1
                if (!reader.IsDBNull(8)) rgb.strC8 = reader.GetString(8);//tel2
                if (!reader.IsDBNull(9)) rgb.strC9 = reader.GetString(9);//tel3
                if (!reader.IsDBNull(10)) rgb.strC11 = reader.GetString(10);//www
                if (!reader.IsDBNull(11)) rgb.strC12 = reader.GetString(11);//email
                if (!reader.IsDBNull(12)) rgb.intC2 = reader.GetInt32(12); //id per
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getEmergencyArr(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tpem_id, tpem_tper_id, tpem_nombre, tpem_relacion, tpem_direccion1, tpem_ciudad, tpem_state, tpem_zip, tpem_country, tpem_telefono1,tpem_telefono2, tpem_telefono3, tpem_email, tpem_direccion2 ";
            comm.CommandText += " from tbl_persona_emergencia " + where;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1); //id per
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//nombre
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//relacion
                if (!reader.IsDBNull(4)) rgb.strC3 = reader.GetString(4);//direccion
                if (!reader.IsDBNull(5)) rgb.strC4 = reader.GetString(5);//ciudad
                if (!reader.IsDBNull(6)) rgb.strC5 = reader.GetString(6);//estado
                if (!reader.IsDBNull(7)) rgb.strC6 = reader.GetString(7);//zip
                if (!reader.IsDBNull(8)) rgb.strC7 = reader.GetString(8);//country
                if (!reader.IsDBNull(9)) rgb.strC8 = reader.GetString(9);//tel1
                if (!reader.IsDBNull(10)) rgb.strC9 = reader.GetString(10);//tel2
                if (!reader.IsDBNull(11)) rgb.strC10 = reader.GetString(11);//tel3
                if (!reader.IsDBNull(12)) rgb.strC11 = reader.GetString(12);//email
                if (!reader.IsDBNull(13)) rgb.strC12 = reader.GetString(13);//direccion2
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getArrExpediente(int emp_id)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tex_id, tex_emp_id, tex_tpe_id, tex_ttc_id, tex_status,tex_fecha_inicio, tex_fecha_fin, tex_salario, tex_bonificacion, tex_commission, tex_other_commission, tex_currency ";
            comm.CommandText += " from tbl_expediente where tex_emp_id=@emp_id";
            comm.Parameters.Add("@emp_id", SqlDbType.Int).Value = emp_id;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1); //empleado id
                if (!reader.IsDBNull(2)) rgb.intC3 = reader.GetInt32(2); //puesto id
                if (!reader.IsDBNull(3)) rgb.intC4 = reader.GetInt32(3); //contrato id
                if (!reader.IsDBNull(4)) rgb.intC5 = reader.GetInt32(4); //estado id
                if (!reader.IsDBNull(5)) rgb.strC1 = reader.GetDateTime(5).ToShortDateString(); //fecha inicio
                if (!reader.IsDBNull(6)) rgb.strC2 = reader.GetDateTime(6).ToShortDateString(); //fecha fin
                if (!reader.IsDBNull(7)) rgb.decC1 = reader.GetDecimal(7); //Salario
                if (!reader.IsDBNull(8)) rgb.decC2 = reader.GetDecimal(8); //Bonificacion
                if (!reader.IsDBNull(9)) rgb.decC3 = reader.GetDecimal(9); //Comission
                if (!reader.IsDBNull(10)) rgb.decC4 = reader.GetDecimal(10); //Other Commission
                if (!reader.IsDBNull(11)) rgb.strC3 = reader.GetString(11);//currency
             
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getExpedienteArr(int emp_id)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tex_tpe_id, tex_ttc_id, tex_fecha_inicio, tex_fecha_fin, tex_salario, tex_bonificacion, tex_commission, tex_other_commission,tex_id , tex_severance, tex_banco, tex_currency, tex_status, tex_parking, tex_agreement ";
            comm.CommandText +="from tbl_expediente ";
            comm.CommandText += "where  tex_emp_id=@tex_emp_id";
            comm.Parameters.Add("@tex_emp_id", SqlDbType.Int).Value = emp_id;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.strC1 = reader.GetInt32(0).ToString(); //nombre puesto
                if (!reader.IsDBNull(1)) rgb.strC2 = reader.GetInt32(1).ToString(); //contrato
                if (!reader.IsDBNull(2)) rgb.strC3 = reader.GetDateTime(2).ToShortDateString(); //fecha inicio
                if (!reader.IsDBNull(3)) rgb.strC4 = reader.GetDateTime(3).ToShortDateString(); //fecha fin
                if (!reader.IsDBNull(4)) rgb.decC1 = reader.GetDecimal(4); //Salario
                if (!reader.IsDBNull(5)) rgb.decC2 = reader.GetDecimal(5); //Bonificacion
                if (!reader.IsDBNull(6)) rgb.decC3 = reader.GetDecimal(6); //Comission
                if (!reader.IsDBNull(7)) rgb.decC4 = reader.GetDecimal(7); //Other Commission
                if (!reader.IsDBNull(8)) rgb.intC1 = reader.GetInt32(8); //id
                if (!reader.IsDBNull(9)) rgb.decC5 = reader.GetDecimal(9); //cesantia (severance)
                if (!reader.IsDBNull(10)) rgb.intC6 = reader.GetInt32(10); //banco
                if (!reader.IsDBNull(11)) rgb.strC5 = reader.GetString(11); //currency
                if (!reader.IsDBNull(12)) rgb.intC7 = reader.GetInt32(12); //status
                if (!reader.IsDBNull(13)) rgb.intC8 = reader.GetInt32(13); //parking
                if (!reader.IsDBNull(14)) rgb.decC6 = reader.GetDecimal(14); //agreement sev
           
           
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getEmployementArrbyCompany(int compid)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select emp_id, tper_nombre, tper_apellido, emp_codigo_empleado, tcu_nombre, tpe_nombre from tbl_cuenta, tbl_empleado, tbl_persona, tbl_puesto_empleado, tbl_expediente where tper_id=emp_tper_id and emp_tcu_id=tcu_id and tex_emp_id=emp_id and tex_fecha_fin is null and tex_tpe_id=tpe_id and tcu_tco_id=@compid";
            comm.Parameters.Add("@compid", SqlDbType.Int).Value = compid;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id empleado
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//apellido
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);//codigo empleado
                if (!reader.IsDBNull(4)) rgb.strC4 = reader.GetString(4);//cuenta
                if (!reader.IsDBNull(5)) rgb.strC5 = reader.GetString(5);//puesto
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static ArrayList getEmployementArrbyCuenta(int cuentaid)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT  tcu_manager FROM tbl_cuenta  WHERE tcu_id = @cuentaid";
            comm.Parameters.Add("@cuentaid", SqlDbType.Int).Value = cuentaid;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();

                if (!reader.IsDBNull(0)) rgb.strC1 = reader.GetString(0);//nombre


                result.Add(rgb);
            }

            reader.Close();
            comm.Parameters.Clear();
            comm.CommandText = "SELECT   tbl_empleado.emp_id, tbl_persona.tper_nombre, tbl_persona.tper_apellido, tbl_empleado.emp_codigo_empleado, tbl_puesto_empleado.tpe_nombre FROM tbl_persona INNER JOIN tbl_empleado ON tbl_persona.tper_id = tbl_empleado.emp_tper_id INNER JOIN tbl_expediente ON tbl_empleado.emp_id = tbl_expediente.tex_emp_id INNER JOIN tbl_puesto_empleado ON tbl_expediente.tex_tpe_id = tbl_puesto_empleado.tpe_id WHERE  (tbl_expediente.tex_fecha_fin IS NULL OR tbl_expediente.tex_fecha_fin = '1/1/1900') AND (tbl_empleado.emp_tcu_id = @cuentaid)";
            comm.Parameters.Add("@cuentaid", SqlDbType.Int).Value = cuentaid;
            reader = comm.ExecuteReader();
            

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id empleado
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//apellido
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);//codigo empleado
                if (!reader.IsDBNull(4)) rgb.strC4 = reader.GetString(4);//puesto
                
                result.Add(rgb);
            }

            reader.Close();
            
           

        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getSuperiors(int empid)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT  tbl_empleado.emp_id from tbl_empleado where emp_codigo_empleado = '1111' ";
            reader = comm.ExecuteReader();

            int tempid = 0;
             while (reader.Read())
             {
                  if (!reader.IsDBNull(0)) tempid = reader.GetInt32(0); //id empleado con codig 0000 pose VP
             }

             reader.Close();
             utility.log("Validando si es VP para que salga el empid :"+ empid+" tempid = "+tempid);
             if (empid == tempid) {
                 comm.CommandText = "SELECT tbl_empleado.emp_id, tbl_persona.tper_nombre, tbl_persona.tper_apellido, tbl_empleado.emp_codigo_empleado, tbl_puesto_empleado.tpe_nombre FROM   tbl_puesto_empleado INNER JOIN  tbl_expediente ON tbl_puesto_empleado.tpe_id = tbl_expediente.tex_tpe_id INNER JOIN  tbl_empleado ON tbl_expediente.tex_emp_id = tbl_empleado.emp_id INNER JOIN tbl_persona ON tbl_empleado.emp_tper_id = tbl_persona.tper_id WHERE   (tbl_puesto_empleado.tpe_superior = 1)";
             }else{

                 comm.CommandText = "SELECT tbl_empleado.emp_id, tbl_persona.tper_nombre, tbl_persona.tper_apellido, tbl_empleado.emp_codigo_empleado, tbl_puesto_empleado.tpe_nombre FROM   tbl_puesto_empleado INNER JOIN  tbl_expediente ON tbl_puesto_empleado.tpe_id = tbl_expediente.tex_tpe_id INNER JOIN  tbl_empleado ON tbl_expediente.tex_emp_id = tbl_empleado.emp_id INNER JOIN tbl_persona ON tbl_empleado.emp_tper_id = tbl_persona.tper_id WHERE  (tbl_empleado.emp_id <> @empid ) AND (tbl_puesto_empleado.tpe_superior = 1)"; 
        }
            comm.Parameters.Add("@empid", SqlDbType.Int).Value = @empid;
            reader = comm.ExecuteReader();


            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id empleado
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//apellido
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);//codigo empleado
                if (!reader.IsDBNull(4)) rgb.strC4 = reader.GetString(4);//puesto

                result.Add(rgb);
            }

            reader.Close();



        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getEmployementArr(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            if (where != "") where =" where "+ where ;

            comm.CommandText = "SELECT tbl_persona.tper_id, tbl_persona.tper_nombre  as Nombre,  tbl_persona.tper_nombre2, tbl_persona.tper_apellido as Apellido , tbl_empleado.emp_codigo_empleado, tbl_cuenta.tcu_nombre AS Cuenta, tbl_puesto_empleado.tpe_nombre AS puesto, tbl_expediente.tex_status FROM    tbl_cuenta INNER JOIN  tbl_empleado ON tbl_cuenta.tcu_id = tbl_empleado.emp_tcu_id INNER JOIN  tbl_expediente ON tbl_empleado.emp_id = tbl_expediente.tex_emp_id LEFT OUTER JOIN  tbl_puesto_empleado ON tbl_expediente.tex_tpe_id = tbl_puesto_empleado.tpe_id RIGHT OUTER JOIN tbl_persona ON tbl_empleado.emp_tper_id = tbl_persona.tper_id";
            comm.CommandText += where+"   ORDER BY Apellido, Nombre";
            
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id persona
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//middle name
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);//apellido
                if (!reader.IsDBNull(4)) rgb.strC4 = reader.GetString(4);//codigo de empleado
                if (!reader.IsDBNull(5)) rgb.strC5 = reader.GetString(5);//cuenta
                if (!reader.IsDBNull(6)) rgb.strC6 = reader.GetString(6);//puesto
                if (!reader.IsDBNull(7)) rgb.intC2 = reader.GetInt32(7);//status
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getArrDocumentbyEmp(int comp_id, int tipo, int emp_id)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlConnection conn2 = null;
        SqlCommand comm = null;
        SqlCommand comm2 = null;
        SqlDataReader reader = null;
        SqlDataReader reader2 = null;
        try
        {
            conn = OpenConnection();
            conn2 = OpenConnection();
            comm = new SqlCommand();
            comm2 = new SqlCommand();
            comm.Connection = conn;
            comm2.Connection = conn2;
            comm.CommandType = CommandType.Text;
            comm2.CommandType = CommandType.Text;

            //Busco los documentos de los empleados segun por compañia
            comm.CommandText = " SELECT  tbl_vista_documento.tdc_id, tbl_tipo_documento.nombre, tbl_vista_documento.obligatorio, tbl_vista_documento.visible ";
            comm.CommandText += " FROM  tbl_vista_documento INNER JOIN tbl_tipo_documento ON tbl_vista_documento.tdc_id = tbl_tipo_documento.id ";
            comm.CommandText += " WHERE  (tbl_tipo_documento.tipo = @tipo) AND (tbl_vista_documento.tco_id = @id_comp) ORDER BY tbl_tipo_documento.orden ";
            comm.Parameters.Add("@id_comp", SqlDbType.Int).Value = comp_id;
            comm.Parameters.Add("@tipo", SqlDbType.Int).Value = tipo;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //docuid
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.intC2 = reader.GetInt32(2);//obligatorio
                if (!reader.IsDBNull(3)) rgb.intC3 = reader.GetInt32(3);//visible
                

                //ahora busco si tiene el documento un empleado y escribo el path

                int agrego = 0;
                comm2.Parameters.Clear();
                comm2.CommandText = " SELECT DISTINCT path, id FROM tbl_empleado_documento WHERE empleado_id=@emp_id AND documento_id= @document ORDER BY id ";
                comm2.Parameters.Add("@emp_id", SqlDbType.Int).Value = emp_id;
                comm2.Parameters.Add("@document", SqlDbType.Int).Value = rgb.intC1;
                reader2= comm2.ExecuteReader();
                while (reader2.Read())
                {
                    agrego = 0;
                    RE_GenericBean r = new RE_GenericBean();
                    r.intC1 = rgb.intC1; r.strC1 = rgb.strC1; r.intC2 = rgb.intC2; r.intC3 = rgb.intC3;
                    if (!reader2.IsDBNull(0)) rgb.strC2 = reader2.GetString(0); //path
                    if (!reader2.IsDBNull(1)) rgb.intC4 = reader2.GetInt32(1); //doc_id
                    r.strC2 = rgb.strC2;
                    r.intC4 = rgb.intC4;
                    result.Add(r);
                    
                    agrego = 1;
                }
                reader2.Close();
                if (agrego == 0) {
                    result.Add(rgb);
                    
                }
                
            }

            reader.Close();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
            if (reader2 != null)
                reader2.Dispose();
            if (comm2 != null)
                comm2.Dispose();
            if (conn2 != null)
            {
                conn2.Close();
                conn2.Dispose();
            }
        }
        return result;
    }
    public static ArrayList getArrDocuments(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT tbl_tipo_documento.id, tbl_tipo_documento.nombre, tbl_tipo_documento.descripcion, tbl_tipo_documento.obligatorio, tbl_tipo_documento.orden, tbl_tipo_documento.tipo, tbl_tdoc.id AS Expr1, tbl_tdoc.nombre";
            comm.CommandText += " FROM tbl_tipo_documento INNER JOIN tbl_tdoc ON tbl_tipo_documento.tipo = tbl_tdoc.id ";//where tbl_tipo_documento.id=@id 

            //comm.CommandText = "select id, nombre, tipo_documento, obligatorio, orden, descripcion  from tbl_tipo_documento " + where;

            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//descripcion
                if (!reader.IsDBNull(3)) rgb.intC2 = reader.GetInt32(3);//obligatorio
                if (!reader.IsDBNull(4)) rgb.intC3 = reader.GetInt32(4);//orden
                if (!reader.IsDBNull(5)) rgb.intC4 = reader.GetInt32(5);//tipo
                if (!reader.IsDBNull(6)) rgb.intC5 = reader.GetInt32(6);//id tabla t_doc
                if (!reader.IsDBNull(7)) rgb.strC4 = reader.GetString(7);//nombre tabla t_doc
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }


    public static ArrayList getArrDocsComp()
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = " SELECT  id, nombre, obligatorio, 1  FROM  tbl_tipo_documento WHERE tipo=1 ORDER BY orden ";

            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.intC2 = reader.GetInt32(2);//obligatorio
                if (!reader.IsDBNull(3)) rgb.intC3 = reader.GetInt32(3);//visible
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getArrDocsPerComps(int comp_id, int tipo)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = " SELECT  tbl_vista_documento.tdc_id, tbl_tipo_documento.nombre, tbl_vista_documento.obligatorio, tbl_vista_documento.visible ";
            comm.CommandText += " FROM  tbl_vista_documento INNER JOIN tbl_tipo_documento ON tbl_vista_documento.tdc_id = tbl_tipo_documento.id ";
            comm.CommandText += " WHERE  (tbl_tipo_documento.tipo = @tipo) AND (tbl_vista_documento.tco_id = @id_comp) ORDER BY tbl_tipo_documento.orden ";
            comm.Parameters.Add("@id_comp", SqlDbType.Int).Value = comp_id;
            comm.Parameters.Add("@tipo", SqlDbType.Int).Value = tipo;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.intC2 = reader.GetInt32(2);//obligatorio
                if (!reader.IsDBNull(3)) rgb.intC3 = reader.GetInt32(3);//visible
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static ArrayList getArrDocumentbyCom(int com_id)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlConnection conn2 = null;
        SqlCommand comm = null;
        SqlCommand comm2 = null;
        SqlDataReader reader = null;
        SqlDataReader reader2 = null;
        try
        {
            conn = OpenConnection();
            conn2 = OpenConnection();
            comm = new SqlCommand();
            comm2 = new SqlCommand();
            comm.Connection = conn;
            comm2.Connection = conn2;
            comm.CommandType = CommandType.Text;
            comm2.CommandType = CommandType.Text;


            comm.CommandText = " SELECT   DISTINCT  tbl_tipo_documento.id, tbl_tipo_documento.nombre, tbl_tipo_documento.obligatorio, tbl_tipo_documento.orden   FROM   tbl_compania_documento RIGHT OUTER JOIN  tbl_tipo_documento ON tbl_compania_documento.documento_id = tbl_tipo_documento.id where tipo=@tipo   ORDER BY orden ";
            comm.Parameters.Add("@com_id", SqlDbType.Int).Value = com_id;
            comm.Parameters.Add("@tipo", SqlDbType.Int).Value = 1;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //tipodocid
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.intC2 = reader.GetInt32(2);//obligatorio
                if (!reader.IsDBNull(3)) rgb.intC3 = 1;//reader.GetInt32(3);//orden


                //ahora busco si tiene el documento una compania y escribo el path

                int agrego = 0;
                comm2.Parameters.Clear();
                comm2.CommandText = " select path, id from tbl_compania_documento where compania_id=@com_id and documento_id= @document order by id ";
                comm2.Parameters.Add("@com_id", SqlDbType.Int).Value = com_id;
                comm2.Parameters.Add("@document", SqlDbType.Int).Value = rgb.intC1;
                reader2 = comm2.ExecuteReader();
                while (reader2.Read())
                {
                    agrego = 0;
                    RE_GenericBean r = new RE_GenericBean();
                    r.intC1 = rgb.intC1; r.strC1 = rgb.strC1; r.intC2 = rgb.intC2; r.intC3 = rgb.intC3;
                    if (!reader2.IsDBNull(0)) rgb.strC2 = reader2.GetString(0); //path
                    if (!reader2.IsDBNull(1)) rgb.intC4 = reader2.GetInt32(1); //doc_id
                    r.strC2 = rgb.strC2;
                    r.intC4 = rgb.intC4;
                    result.Add(r);
                    utility.log("Agrego a Docs " + rgb.intC4 + " " + rgb.intC1 + " " + rgb.strC2);
                    agrego = 1;
                }
                reader2.Close();
                if (agrego == 0)
                {
                    result.Add(rgb);
                    utility.log("Agrego a Docs " + rgb.intC4 + " " + rgb.intC1 + " " + rgb.strC2);
                }

            }

            reader.Close();
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
            if (reader2 != null)
                reader2.Dispose();
            if (comm2 != null)
                comm2.Dispose();
            if (conn2 != null)
            {
                conn2.Close();
                conn2.Dispose();
            }
        }
        return result;
    }


    public static ArrayList getArrStatus(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select id, nombre, orden from tbl_status " + where+ " order by orden";
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.intC2 = reader.GetInt32(2);//orden
                
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getArrCompanyBank(string where)
    {
        //muestra los bancos en el dropdownlist en el pop up para poder seleccionar el deseado.

        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select id, nombre from tbl_banco " + where; // selecciona el id, nombre 

            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre


                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static RE_GenericBean getCuentasbyCompania(int bank)
    {
        //lo utilicé para poder cargar los datos del registro deseado para poderlo modificar en el pop up.

        RE_GenericBean rgb = new RE_GenericBean();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "SELECT tbl_company_bank.id, tbl_company_bank.idbank, tbl_company_bank.account, tbl_company_bank.account_type, tbl_company_bank.enable, tbl_banco.nombre FROM tbl_company_bank INNER JOIN tbl_banco ON tbl_company_bank.idbank = tbl_banco.id WHERE tbl_company_bank.id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = bank;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1);//id banco
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//account
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//account type
                if (!reader.IsDBNull(4)) rgb.intC3 = reader.GetInt32(4);//enable
                if (!reader.IsDBNull(5)) rgb.strC3 = reader.GetString(5);// nombre banco    
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return rgb;
    }

    public static ArrayList getArrCuentasbyCompany(int id)
    {
        //este método lo usé para poder llenar la tabla en el formulario para visualizar los datos de la tabla en la base de datos

        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "SELECT tbl_company_bank.id, tbl_company_bank.idbank, tbl_company_bank.account, tbl_company_bank.account_type, tbl_company_bank.enable, tbl_banco.nombre FROM tbl_company_bank INNER JOIN tbl_banco ON tbl_company_bank.idbank = tbl_banco.id WHERE tbl_company_bank.idcompany=@id";//tbl_compania.tco_id
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1); //id BANK

                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//account
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//account type
                if (!reader.IsDBNull(4)) rgb.intC4 = reader.GetInt32(4); //enable
                if (!reader.IsDBNull(5)) rgb.strC3 = reader.GetString(5);//nombre banco


                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }



    //%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
    public static RE_GenericBean getTipoAnotacion(int id)
    {
        RE_GenericBean rgb = new RE_GenericBean();        
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tan_id, tan_tco_id, tan_nombre, tan_descripcion from tbl_tipo_anotacion where tan_id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id anotacion
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1);//compania
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//nombre
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//descripcion
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return rgb;
    }

    public static RE_GenericBean getPuestoEmpleado(int id)
    {
        RE_GenericBean rgb = new RE_GenericBean();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tpe_id, tpe_tco_id, tpe_nombre, tpe_descripcion, tpe_salario_minimo, tpe_salario_maximo, tpe_currency, tpe_aumento, tpe_superior from tbl_puesto_empleado where tpe_id=@id ";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1);//compania
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//nombre
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//descripcion
                if (!reader.IsDBNull(4)) rgb.decC1 = reader.GetDecimal(4);//Salario minimo
                if (!reader.IsDBNull(5)) rgb.decC2 = reader.GetDecimal(5);//salario maximo
                if (!reader.IsDBNull(6)) rgb.strC3 = reader.GetString(6);//moneda
                if (!reader.IsDBNull(7)) rgb.decC3 = reader.GetDecimal(7);//aumento
                if (!reader.IsDBNull(8)) rgb.intC4 = reader.GetInt32(8);//superior
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return rgb;
    }

    public static RE_GenericBean getCuenta(int id)
    {
        RE_GenericBean rgb = new RE_GenericBean();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tcu_id, tcu_tco_id, tcu_nombre, tcu_descripcion, tcu_manager from tbl_cuenta where tcu_id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1);//compania
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//nombre
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//descripcion
                if (!reader.IsDBNull(4)) rgb.strC3 = reader.GetString(4);//manager
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return rgb;
    }

    public static RE_GenericBean getLocation(int id)
    {
        RE_GenericBean rgb = new RE_GenericBean();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select loc_id, loc_tco_id, loc_id_interno, loc_name, loc_addr1, loc_addr2, loc_city, loc_state, loc_zip, loc_country, loc_phone, loc_fax from tbl_localidad where loc_id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1);//compania
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//interno
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//nombre
                if (!reader.IsDBNull(4)) rgb.strC3 = reader.GetString(4);//dir1
                if (!reader.IsDBNull(5)) rgb.strC4 = reader.GetString(5);//dir2
                if (!reader.IsDBNull(6)) rgb.strC5 = reader.GetString(6);//city
                if (!reader.IsDBNull(7)) rgb.strC6 = reader.GetString(7);//state
                if (!reader.IsDBNull(8)) rgb.strC7 = reader.GetString(8);//zip
                if (!reader.IsDBNull(9)) rgb.strC8 = reader.GetString(9);//country
                if (!reader.IsDBNull(10)) rgb.strC9 = reader.GetString(10);//phone
                if (!reader.IsDBNull(11)) rgb.strC10 = reader.GetString(11);//fax
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return rgb;
    }

    public static RE_GenericBean getTipoContrato(int id)
    {
        RE_GenericBean rgb = new RE_GenericBean();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select ttc_id, ttc_tco_id, ttc_nombre, ttc_descripcion from tbl_tipo_contrato where ttc_id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1);//compania
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//nombre
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//descripcion
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return rgb;
    }

    public static RE_GenericBean getCompania(int id)
    {
        RE_GenericBean rgb = new RE_GenericBean();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tco_id, tco_nombre, tco_nit, tco_direccion1, tco_direccion2, tco_ciudad, tco_state, tco_zip, tco_country,";
            comm.CommandText += " tco_telefono1, tco_telefono2, tco_telefono3, tco_fax, tco_website, tco_contact_email, tco_logo from tbl_compania where tco_id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//nit
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);//direccion1
                if (!reader.IsDBNull(4)) rgb.strC4 = reader.GetString(4);//direccion2
                if (!reader.IsDBNull(5)) rgb.strC5 = reader.GetString(5);//ciudad
                if (!reader.IsDBNull(6)) rgb.strC6 = reader.GetString(6);//state
                if (!reader.IsDBNull(7)) rgb.strC7 = reader.GetString(7);//zip
                if (!reader.IsDBNull(8)) rgb.intC2 = reader.GetInt32(8);//country
                if (!reader.IsDBNull(9)) rgb.strC9 = reader.GetString(9);//tel1
                if (!reader.IsDBNull(10)) rgb.strC10 = reader.GetString(10);//tel2
                if (!reader.IsDBNull(11)) rgb.strC11 = reader.GetString(11);//tel3
                if (!reader.IsDBNull(12)) rgb.strC12 = reader.GetString(12);//fax
                if (!reader.IsDBNull(13)) rgb.strC13 = reader.GetString(13);//www
                if (!reader.IsDBNull(14)) rgb.strC14 = reader.GetString(14);//email
                if (!reader.IsDBNull(15)) rgb.strC15 = reader.GetString(15);//logo
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return rgb;
    }

    public static RE_GenericBean getPersonaInf(int per_id, int emp_id)
    {
        RE_GenericBean rgb = new RE_GenericBean();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "SELECT  tbl_persona.tper_id, tbl_persona.tper_nombre, tbl_persona.tper_nombre2, tbl_persona.tper_apellido, tbl_persona.tper_apellido2,  tbl_persona.tper_apellido_casada, tbl_persona.tper_genero, tbl_persona.tper_estado_civil, tbl_persona.tper_fecha_nacimiento, tbl_persona.tper_nit, ";
            comm.CommandText += "   tbl_persona.tper_igss, tbl_empleado.emp_codigo_empleado, tbl_empleado.emp_id, tbl_empleado.emp_tcu_id, tbl_cuenta.tcu_tco_id,  CONVERT(varchar, tbl_empleado.emp_hired_date, 101) AS hiredate, CONVERT(varchar, tbl_empleado.emp_end_date, 101) AS enddate,   tbl_persona.tper_idper, tbl_persona.tper_foto, tbl_persona.tper_irtra, tbl_persona.tper_tipo_idper, tbl_empleado.emp_jefe ";
            comm.CommandText += " FROM   tbl_cuenta INNER JOIN  tbl_empleado ON tbl_cuenta.tcu_id = tbl_empleado.emp_tcu_id RIGHT OUTER JOIN  tbl_persona ON tbl_empleado.emp_tper_id = tbl_persona.tper_id WHERE     (tbl_persona.tper_id = @per_id)";
            comm.Parameters.Add("@per_id", SqlDbType.Int).Value = per_id;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id persona
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//nombre2
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);//apellido
                if (!reader.IsDBNull(4)) rgb.strC4 = reader.GetString(4);//apellido2
                if (!reader.IsDBNull(5)) rgb.strC5 = reader.GetString(5);//apellido_casada
                if (!reader.IsDBNull(6)) rgb.strC6 = reader.GetString(6);//genero
                if (!reader.IsDBNull(7)) rgb.strC7 = reader.GetString(7);//estado Civil
                if (!reader.IsDBNull(8)) rgb.strC8 = reader.GetDateTime(8).ToShortDateString();//Fecha Nacimiento
                if (!reader.IsDBNull(9)) rgb.strC9 = reader.GetString(9);//nit
                if (!reader.IsDBNull(10)) rgb.strC10 = reader.GetString(10);//igss
                if (!reader.IsDBNull(11)) rgb.strC11 = reader.GetString(11);//codigo empleado
                if (!reader.IsDBNull(12)) rgb.intC2 = reader.GetInt32(12); //idempleado
                if (!reader.IsDBNull(13)) rgb.intC3 = reader.GetInt32(13); //cuenta
                if (!reader.IsDBNull(14)) rgb.intC4 = reader.GetInt32(14); //company
                if (!reader.IsDBNull(15)) rgb.strC12 = reader.GetString(15); //hired date
                if (!reader.IsDBNull(16)) rgb.strC13 = reader.GetString(16); //end dte
                if (!reader.IsDBNull(17)) rgb.strC14 = reader.GetString(17); //cedula o dpi
                if (!reader.IsDBNull(18)) rgb.strC15 = reader.GetString(18); //foto
                if (!reader.IsDBNull(19)) rgb.strC16 = reader.GetString(19); //irtra
                if (!reader.IsDBNull(20)) rgb.intC5 = int.Parse(reader.GetString(20)); //tipo id personala
                if (!reader.IsDBNull(21)) rgb.intC6 = reader.GetInt32(21); //id jefe


            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return rgb;
    }

    public static RE_GenericBean getEmergency(int id)
    {
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tpem_id, tpem_tper_id, tpem_nombre, tpem_relacion, tpem_direccion1, tpem_ciudad, tpem_state, tpem_zip, tpem_country, tpem_telefono1,tpem_telefono2, tpem_telefono3, tpem_email, tpem_direccion2 ";
            comm.CommandText += " from tbl_persona_emergencia where tpem_id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            reader = comm.ExecuteReader();
            
            if (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1); //id per
                if (!reader.IsDBNull(2)) rgb.strC1 = reader.GetString(2);//nombre
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//relacion
                if (!reader.IsDBNull(4)) rgb.strC3 = reader.GetString(4);//direccion
                if (!reader.IsDBNull(5)) rgb.strC4 = reader.GetString(5);//ciudad
                if (!reader.IsDBNull(6)) rgb.strC5 = reader.GetString(6);//estado
                if (!reader.IsDBNull(7)) rgb.strC6 = reader.GetString(7);//zip
                if (!reader.IsDBNull(8)) rgb.strC7 = reader.GetString(8);//country
                if (!reader.IsDBNull(9)) rgb.strC8 = reader.GetString(9);//tel1
                if (!reader.IsDBNull(10)) rgb.strC9 = reader.GetString(10);//tel2
                if (!reader.IsDBNull(11)) rgb.strC10 = reader.GetString(11);//tel3
                if (!reader.IsDBNull(12)) rgb.strC11 = reader.GetString(12);//email
                if (!reader.IsDBNull(13)) rgb.strC12 = reader.GetString(13);//dir2
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return rgb;
    }

    public static RE_GenericBean getContact(int id)
    {
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tpd_id, tpd_direccion1, tpd_direccion2, tpd_ciudad, tpd_state, tpd_zip, tpd_country, tpd_telefono1, tpd_telefono2, tpd_telefono3, tpd_work_email, tpd_personal_email, tpd_tper_id ";
            comm.CommandText += " from tbl_persona_direccion where tpd_id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//direccion1
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//direccion2
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);//ciudad
                if (!reader.IsDBNull(4)) rgb.strC4 = reader.GetString(4);//state
                if (!reader.IsDBNull(5)) rgb.strC5 = reader.GetString(5);//zip
                if (!reader.IsDBNull(6)) rgb.strC6 = reader.GetString(6);//country
                if (!reader.IsDBNull(7)) rgb.strC7 = reader.GetString(7);//tel1
                if (!reader.IsDBNull(8)) rgb.strC8 = reader.GetString(8);//tel2
                if (!reader.IsDBNull(9)) rgb.strC9 = reader.GetString(9);//tel3
                if (!reader.IsDBNull(10)) rgb.strC11 = reader.GetString(10);//w email
                if (!reader.IsDBNull(11)) rgb.strC12 = reader.GetString(11);//personal email
                if (!reader.IsDBNull(12)) rgb.intC2 = reader.GetInt32(12); //id per
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return rgb;
    }

    public static RE_GenericBean getExpediente(int id)
    {
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tex_id, tex_emp_id, tex_tpe_id, tex_ttc_id, tex_status,tex_fecha_inicio, tex_fecha_fin, tex_salario, tex_bonificacion, tex_commission, tex_other_commission, tex_currency, tex_banco ";
            comm.CommandText += " from tbl_expediente where tex_id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.intC2 = reader.GetInt32(1); //empleado id
                if (!reader.IsDBNull(2)) rgb.intC3 = reader.GetInt32(2); //puesto id
                if (!reader.IsDBNull(3)) rgb.intC4 = reader.GetInt32(3); //contrato id
                if (!reader.IsDBNull(4)) rgb.intC5 = reader.GetInt32(4); //estado id
                if (!reader.IsDBNull(5)) rgb.strC1 = reader.GetDateTime(5).ToShortDateString(); //fecha inicio
                if (!reader.IsDBNull(6)) rgb.strC2 = reader.GetDateTime(6).ToShortDateString(); //fecha fin
                if (!reader.IsDBNull(7)) rgb.decC1 = reader.GetDecimal(7); //Salario
                if (!reader.IsDBNull(8)) rgb.decC2 = reader.GetDecimal(8); //Bonificacion
                if (!reader.IsDBNull(9)) rgb.decC3 = reader.GetDecimal(9); //Comission
                if (!reader.IsDBNull(10)) rgb.decC4 = reader.GetDecimal(10); //Other Commission
                if (!reader.IsDBNull(11)) rgb.strC3 = reader.GetString(11);//currency
                if (!reader.IsDBNull(12)) rgb.intC6 = reader.GetInt32(12);//bank
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return rgb;
    }

    public static ArrayList getArrBancos(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select id, nombre, descripcion from tbl_banco " + where;

            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//descripcion

                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static ArrayList getArrBancosCountry(int id)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select tbl_banco.id, tbl_banco.nombre, tbl_banco.descripcion, tbl_banco.country, tbl_country.name FROM tbl_banco INNER JOIN tbl_country ON tbl_banco.country = tbl_country.id ";
            // comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC3 = reader.GetString(2);//description
                if (!reader.IsDBNull(3)) rgb.intC2 = reader.GetInt32(3);//Country id
                if (!reader.IsDBNull(4)) rgb.strC4 = reader.GetString(4);//Country name

                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static ArrayList getArrBancosbyCompany(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "SELECT distinct tbl_company_bank.id, tbl_banco.nombre, tbl_company_bank.idbank, tbl_company_bank.idcompany, tbl_company_bank.account,  tbl_company_bank.enable FROM  tbl_company_bank INNER JOIN tbl_banco ON tbl_company_bank.id = tbl_banco.id " + where;
            utility.log("Select bancos by company " + comm.CommandText);
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre banco
                if (!reader.IsDBNull(2)) rgb.intC2 = reader.GetInt32(2);//idbank
                if (!reader.IsDBNull(3)) rgb.intC3 = reader.GetInt32(3);//idcompany
                if (!reader.IsDBNull(4)) rgb.strC2 = reader.GetString(4);//account
                if (!reader.IsDBNull(5)) rgb.intC4 = reader.GetInt32(5);//enable



                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static ArrayList getArrCountries(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select id, name from tbl_country " + where;

            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
               

                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static ArrayList getArrEmpleadoCuentaBanco(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select emp_accbank_id, emp_accbank_acc#, emp_accbank_type, emp_accbank_empid, emp_accbank_status, emp_accbank_bank from tbl_empleado_accbank " + where;

            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //emp_accbank_id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//emp_accbank_acc#
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//empp_accbank_type
                if (!reader.IsDBNull(3)) rgb.intC2 = reader.GetInt32(3);//empp_accbank_empid
                if (!reader.IsDBNull(4)) rgb.intC3 = reader.GetInt32(4);//empp_accbank_status
                if (!reader.IsDBNull(5)) rgb.intC4 = reader.GetInt32(5);//empp_accbank_bank

                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static DataSet getArrPayroll(string where)
    {
        DataSet result = new DataSet();
        result.Tables.Add("tabla");
        result.Tables[0].Columns.Add("ID");
        result.Tables[0].Columns.Add("NAME");
        result.Tables[0].Columns.Add("START DATE");
        result.Tables[0].Columns.Add("END DATE");
        result.Tables[0].Columns.Add("creator");
        result.Tables[0].Columns.Add("COMPLETED");
      
        result.Tables[0].Columns.Add("comments");
        result.Tables[0].Columns.Add("costextraorder");

        
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select id, name, convert(varchar,startdate,101), convert(varchar,enddate,101), creator, completed, comments, costextraorder from tbl_payroll " + where;

            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                RE_GenericBean rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetString(2);//startdate
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);//enddate
                if (!reader.IsDBNull(4)) rgb.strC4 = reader.GetString(4);//creator
                if (!reader.IsDBNull(5)) rgb.intC2 = reader.GetInt32(5); //completed
              
                if (!reader.IsDBNull(6)) rgb.strC5 = reader.GetString(6);//comments
                if (!reader.IsDBNull(7)) rgb.decC1 = reader.GetDecimal(7);//cost extra order

                object[] obj = {rgb.intC1, rgb.strC1.ToUpper(), rgb.strC2, rgb.strC3, rgb.strC4, rgb.intC2, rgb.strC5, rgb.decC1};

                result.Tables[0].Rows.Add(obj);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static string getTextoControl(int country, string nombrecontrol)
    {
        string result = nombrecontrol;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();

        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select texto  from tbl_country_label where country=@country and labelname=@nombrecontrol ";

            comm.Parameters.Add("@country", SqlDbType.Int).Value = country;
            comm.Parameters.Add("@nombrecontrol", SqlDbType.VarChar).Value = nombrecontrol;

            reader = comm.ExecuteReader();
            comm.Parameters.Clear();

            while (reader.Read())
            {

                if (!reader.IsDBNull(0)) result = reader.GetString(0);

            }



        }
        catch (Exception e)
        {

            utility.log(e.Message.ToString());
            return nombrecontrol;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;

    }

    internal static string getContenidoControl(int country, string nombrecontrol)
    {
        string result = nombrecontrol;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();

        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select contenido  from tbl_country_label where country=@country and labelname=@nombrecontrol ";

            comm.Parameters.Add("@country", SqlDbType.Int).Value = country;
            comm.Parameters.Add("@nombrecontrol", SqlDbType.VarChar).Value = nombrecontrol;

            reader = comm.ExecuteReader();
            comm.Parameters.Clear();

            while (reader.Read())
            {

                if (!reader.IsDBNull(0)) result = reader.GetString(0);

            }



        }
        catch (Exception e)
        {

            utility.log(e.Message.ToString());
            return nombrecontrol;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;

    }


    internal static int getMaxLongitudControl(int country, string nombrecontrol)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();

        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select max_long  from tbl_country_label where country=@country and labelname=@nombrecontrol ";

            comm.Parameters.Add("@country", SqlDbType.Int).Value = country;
            comm.Parameters.Add("@nombrecontrol", SqlDbType.VarChar).Value = nombrecontrol;

            reader = comm.ExecuteReader();
            comm.Parameters.Clear();

            while (reader.Read())
            {

                if (!reader.IsDBNull(0)) result = reader.GetInt32(0);

            }



        }
        catch (Exception e)
        {

            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;

    }
    internal static DataTable getOptionsControl(int country, string nombrecontrol)
    {
        ArrayList a = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        DataTable result = new DataTable();
        result.TableName = "option";
        result.Columns.Add("id");
        result.Columns.Add("option");
        result.Rows.Add("0","Select One");
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
           
            
            comm.CommandText = "select opcion  from tbl_country_label where country=@country and labelname=@nombrecontrol and tipo='ddl'";
            comm.Parameters.Add("@country", SqlDbType.Int).Value = country;
            comm.Parameters.Add("@nombrecontrol", SqlDbType.VarChar).Value = nombrecontrol;

            reader = comm.ExecuteReader();
            comm.Parameters.Clear();
            int opcion= 0;
            while (reader.Read())
            {

                if (!reader.IsDBNull(0)) opcion = reader.GetInt32(0);

            }

            reader.Close();

            //buscando los items de la opcion
            comm.CommandText = "select id, texto   from tbl_opcion_item where opcion=@opcion ";
            comm.Parameters.Add("@opcion", SqlDbType.Int).Value = opcion;
           

            reader = comm.ExecuteReader();
            comm.Parameters.Clear();
           
            while (reader.Read())
            {
                string[] item= new string[2];
                if (!reader.IsDBNull(0)) {
                    item[0] = reader.GetInt32(0).ToString();
                    item[1] = reader.GetString(1);
                }
                result.Rows.Add(item[0], item[1]);
            }



        }
        catch (Exception e)
        {

            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }


    public static int getIdNombreTipoDoc(string nombre) {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select id from tbl_tipo_documento where upper(nombre)=@nombre";
            comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = nombre.ToUpper().Trim();
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) result = reader.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static int getIDCompany()
    {
        int ID = new int();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = " select COALESCE(max(tco_id)+1,1) from tbl_compania "; 
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) ID = reader.GetInt32(0); //id
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return ID;
    }


   public static RE_GenericBean getBank(int id)
    {
        RE_GenericBean rgb = new RE_GenericBean();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select id, nombre, descripcion, country from tbl_banco where id=@id"; //toma los valores de la tabla donde el id sea igual al de el seleccionado
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//Nombre
                if (!reader.IsDBNull(2)) rgb.strC3 = reader.GetString(2);//descripcion
                if (!reader.IsDBNull(3)) rgb.intC2 = reader.GetInt32(3);//country
                

            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return rgb;
    }

   public static int getCountBankEmployee(int countbankId)
   {
       int result = 0;
       SqlConnection conn = null;
       SqlCommand comm = null;
       SqlDataReader reader = null;
       try
       {
           conn = OpenConnection();
           comm = new SqlCommand();
           comm.Connection = conn;
           comm.CommandType = CommandType.Text;


           comm.CommandText = "SELECT * FROM tbl_empleado_accbank where emp_accbank_bank = @id";
           comm.Parameters.Add("@id", SqlDbType.Int).Value = countbankId;

           reader = comm.ExecuteReader();

           if (reader.Read())
           {
               result = result + 1;
               if (!reader.IsDBNull(0)) result = reader.GetInt32(0);
           }
       }
       catch (Exception e)
       {
           utility.log(e.Message.ToString());
           return 0;
       }
       finally
       {
           if (reader != null)
               reader.Dispose();
           if (comm != null)
               comm.Dispose();
           if (conn != null)
           {
               conn.Close();
               conn.Dispose();
           }
       }
       return result;
   }
    public static ArrayList getArrTypeDoc(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select id, nombre from tbl_tdoc where enable=1 " + where; // selecciona el id, nombre donde el enable=1 de la tabla tdoc

            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre


                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static ArrayList getArrDocument(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = " SELECT  id, nombre, obligatorio, orden FROM   tbl_tipo_documento " + where + " ORDER BY orden ";

            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//nombre
                if (!reader.IsDBNull(2)) rgb.intC2 = reader.GetInt32(2);//obligatorio
                if (!reader.IsDBNull(3)) rgb.intC3 = reader.GetInt32(3);//orden
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }


    public static RE_GenericBean getDocumentsType1(int id)
    {
        RE_GenericBean rgb = new RE_GenericBean();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "SELECT tbl_tipo_documento.id, tbl_tipo_documento.nombre, tbl_tipo_documento.descripcion, tbl_tipo_documento.obligatorio, tbl_tipo_documento.orden, tbl_tipo_documento.tipo, tbl_tdoc.id AS Expr1, tbl_tdoc.nombre";
            comm.CommandText += " FROM tbl_tipo_documento INNER JOIN tbl_tdoc ON tbl_tipo_documento.tipo = tbl_tdoc.id where tbl_tipo_documento.id=@id "; // toma los campos deseados de la tabla documento y tipo documento en donde el campo id de tabla documento sea igual al parametro
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//Nombre
                if (!reader.IsDBNull(2)) rgb.strC3 = reader.GetString(2);//descripcion
                if (!reader.IsDBNull(3)) rgb.intC2 = reader.GetInt32(3);//obligatorio
                if (!reader.IsDBNull(4)) rgb.intC3 = reader.GetInt32(4);//orden
                if (!reader.IsDBNull(5)) rgb.intC4 = reader.GetInt32(5);// tipo documento
                if (!reader.IsDBNull(6)) rgb.intC5 = reader.GetInt32(6);// id tabla t_doc
                if (!reader.IsDBNull(7)) rgb.strC4 = reader.GetString(7);//nombre tabla t_doc
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return rgb;
    }

    public static int getDocEmp(int docId)
    { // Retorna el conteo usuarios que estan usando ese documento en sus perfiles,  devuleve 0 si ningun usuario tiene asociado el doc 
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "SELECT * FROM tbl_empleado_documento where documento_id = @id"; // selecciona los campos de la tabla empleado donde el campo sea igual al parametro
            comm.Parameters.Add("@id", SqlDbType.Int).Value = docId;

            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                result = result + 1;
                if (!reader.IsDBNull(0)) result = reader.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getDocumentosTipo(int id_com, int tipo)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rg = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = " SELECT  id, nombre, 0 AS obligatorio, 0 AS visible, orden  FROM  tbl_tipo_documento ";
            comm.CommandText += " WHERE  (tipo = @tipo) AND (id NOT IN  (SELECT  tbl_vista_documento.tdc_id  FROM  tbl_vista_documento INNER JOIN tbl_tipo_documento AS tbl_tipo_documento_1 ";
            comm.CommandText += " ON tbl_vista_documento.tdc_id = tbl_tipo_documento_1.id  WHERE  (tbl_vista_documento.tco_id = @id_comp) AND (tbl_tipo_documento_1.tipo = @tipo))) ";
            comm.CommandText += " UNION  SELECT  tbl_vista_documento_1.tdc_id, tbl_tipo_documento_2.nombre, tbl_vista_documento_1.obligatorio, tbl_vista_documento_1.visible, tbl_tipo_documento_2.orden ";
            comm.CommandText += " FROM  tbl_vista_documento AS tbl_vista_documento_1 INNER JOIN  tbl_tipo_documento AS tbl_tipo_documento_2 ON tbl_vista_documento_1.tdc_id = tbl_tipo_documento_2.id ";
            comm.CommandText += " WHERE  (tbl_vista_documento_1.tco_id = @id_comp) AND (tbl_tipo_documento_2.tipo = @tipo)  ORDER BY orden ";
            comm.Parameters.Add("@tipo", SqlDbType.Int).Value = tipo;
            comm.Parameters.Add("@id_comp", SqlDbType.Int).Value = id_com;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rg = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rg.intC1 = reader.GetInt32(0);//ID Documento
                if (!reader.IsDBNull(1)) rg.strC1 = reader.GetString(1);//Nombre Documento
                if (!reader.IsDBNull(2)) rg.intC2 = reader.GetInt32(2);//Obligatorio
                if (!reader.IsDBNull(3)) rg.intC3 = reader.GetInt32(3);//Visible
                result.Add(rg);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getDocumentosTipo2(int tipo)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rg = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = " SELECT  id  FROM  tbl_tipo_documento  WHERE  (tipo = @tipo)  ORDER BY orden ";
            comm.Parameters.Add("@tipo", SqlDbType.Int).Value = tipo;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rg = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rg.intC1 = reader.GetInt32(0);//ID Documento
                result.Add(rg);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }



    // ************************************************** insert's **************************************************************************
    // **                                                                                                                                  **
    // **                                               Espacio para Insert's                                                              **
    // **                                                                                                                                  **
    // **                                                                                                                                  **
    // ************************************************** insert's **************************************************************************

    public static bool InsertCompanyBank(RE_GenericBean combank, bool update)
    {
        //este método se usó para poder ingresar un nuevo registro o actualizar uno ya hecho

        bool result = false;

        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();

        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            if (!update)
            {
                comm.CommandText = "insert into tbl_company_bank (idbank, idcompany, account, account_type, enable)";
                comm.CommandText += "values (@idbank,@idcompany, @account,@account_type,@enable)";
                comm.Parameters.Add("@idbank", SqlDbType.Int).Value = combank.intC1;//id bank
                comm.Parameters.Add("@idcompany", SqlDbType.Int).Value = combank.intC2;//id company
                comm.Parameters.Add("@account", SqlDbType.VarChar).Value = combank.strC1;//numero cuenta
                comm.Parameters.Add("@account_type", SqlDbType.VarChar).Value = combank.strC2;//typo cuenta
                comm.Parameters.Add("@enable", SqlDbType.Int).Value = combank.intC3;//enable
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            else
            {
                comm.CommandText = "update tbl_company_bank set idbank=@idbank, idcompany=@idcompany, account=@account, account_type=@acc_type, enable=@enable ";
                comm.CommandText += "where id=@id";
                comm.Parameters.Add("@id", SqlDbType.Int).Value = combank.intC4;//id
                comm.Parameters.Add("@idbank", SqlDbType.Int).Value = combank.intC1;// id bank
                comm.Parameters.Add("@idcompany", SqlDbType.Int).Value = combank.intC2;//id company
                comm.Parameters.Add("@account", SqlDbType.VarChar).Value = combank.strC1;// account   
                comm.Parameters.Add("@acc_type", SqlDbType.VarChar).Value = combank.strC2;// account type
                comm.Parameters.Add("@enable", SqlDbType.Int).Value = combank.intC3;//enable
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            result = true;

        }
        catch (Exception e)
        {

            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

   public static bool InsertaCompanyInfoGeneral(RE_GenericBean company, bool update) { 
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (!update)
            {
                comm.CommandText = "insert into tbl_compania (tco_id, tco_nombre, tco_nit, tco_direccion1, tco_direccion2, tco_ciudad, tco_state, tco_zip, tco_country, tco_telefono1, tco_telefono2, tco_telefono3, tco_fax, tco_website, tco_contact_email) ";
                comm.CommandText += "values ((select COALESCE(max(tco_id)+1,1) from tbl_compania), @tco_nombre, @tco_nit, @tco_direccion1, @tco_direccion2, @tco_ciudad, @tco_state, @tco_zip, @tco_country, @tco_telefono1, @tco_telefono2, @tco_telefono3, @tco_fax, @tco_website, @tco_contact_email)";
                comm.Parameters.Add("@tco_nombre", SqlDbType.VarChar).Value = company.strC1;
                comm.Parameters.Add("@tco_nit", SqlDbType.VarChar).Value = company.strC2;
                comm.Parameters.Add("@tco_direccion1", SqlDbType.VarChar).Value = company.strC3;
                comm.Parameters.Add("@tco_direccion2", SqlDbType.VarChar).Value = company.strC4;
                comm.Parameters.Add("@tco_ciudad", SqlDbType.VarChar).Value = company.strC5;
                comm.Parameters.Add("@tco_state", SqlDbType.VarChar).Value = company.strC6;
                comm.Parameters.Add("@tco_zip", SqlDbType.VarChar).Value = company.strC7;
                comm.Parameters.Add("@tco_country", SqlDbType.Int).Value = company.intC2;
                comm.Parameters.Add("@tco_telefono1", SqlDbType.VarChar).Value = company.strC9;
                comm.Parameters.Add("@tco_telefono2", SqlDbType.VarChar).Value = company.strC10;
                comm.Parameters.Add("@tco_telefono3", SqlDbType.VarChar).Value = company.strC11;
                comm.Parameters.Add("@tco_fax", SqlDbType.VarChar).Value = company.strC12;
                comm.Parameters.Add("@tco_website", SqlDbType.VarChar).Value = company.strC13;
                comm.Parameters.Add("@tco_contact_email", SqlDbType.VarChar).Value = company.strC14;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            } else {
                comm.CommandText = "update tbl_compania set tco_nombre=@tco_nombre, tco_nit=@tco_nit, tco_direccion1=@tco_direccion1, tco_direccion2=@tco_direccion2, tco_ciudad=@tco_ciudad,";
                comm.CommandText += " tco_state=@tco_state, tco_zip=@tco_zip, tco_country=@tco_country, tco_telefono1=@tco_telefono1, tco_telefono2=@tco_telefono2, tco_telefono3=@tco_telefono3,";
                comm.CommandText += " tco_fax=@tco_fax, tco_website=@tco_website, tco_contact_email=@tco_contact_email where tco_id=@tco_id";
                comm.Parameters.Add("@tco_id", SqlDbType.Int).Value = company.intC1;
                comm.Parameters.Add("@tco_nombre", SqlDbType.VarChar).Value = company.strC1;
                comm.Parameters.Add("@tco_nit", SqlDbType.VarChar).Value = company.strC2;
                comm.Parameters.Add("@tco_direccion1", SqlDbType.VarChar).Value = company.strC3;
                comm.Parameters.Add("@tco_direccion2", SqlDbType.VarChar).Value = company.strC4;
                comm.Parameters.Add("@tco_ciudad", SqlDbType.VarChar).Value = company.strC5;
                comm.Parameters.Add("@tco_state", SqlDbType.VarChar).Value = company.strC6;
                comm.Parameters.Add("@tco_zip", SqlDbType.VarChar).Value = company.strC7;
                comm.Parameters.Add("@tco_country", SqlDbType.Int).Value = company.intC2;
                comm.Parameters.Add("@tco_telefono1", SqlDbType.VarChar).Value = company.strC9;
                comm.Parameters.Add("@tco_telefono2", SqlDbType.VarChar).Value = company.strC10;
                comm.Parameters.Add("@tco_telefono3", SqlDbType.VarChar).Value = company.strC11;
                comm.Parameters.Add("@tco_fax", SqlDbType.VarChar).Value = company.strC12;
                comm.Parameters.Add("@tco_website", SqlDbType.VarChar).Value = company.strC13;
                comm.Parameters.Add("@tco_contact_email", SqlDbType.VarChar).Value = company.strC14;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            result = true;
            transaction.Commit();
        } catch (Exception e) {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            return false;
        } finally {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null) {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }


    public static bool InsertaLocation(RE_GenericBean location, bool update)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (!update)
            {
                comm.CommandText = "insert into tbl_localidad (loc_id, loc_tco_id, loc_id_interno, loc_name, loc_addr1, loc_addr2, loc_city, loc_state, loc_zip, loc_country, loc_phone, loc_fax)";
                comm.CommandText += " values ((select COALESCE(max(loc_id)+1,1) from tbl_localidad), @loc_tco_id, @loc_id_interno, @loc_name, @loc_addr1, @loc_addr2, @loc_city, @loc_state, @loc_zip, @loc_country, @loc_phone, @loc_fax)";
                comm.Parameters.Add("@loc_tco_id", SqlDbType.Int).Value = 1;
                comm.Parameters.Add("@loc_id_interno", SqlDbType.VarChar).Value = location.strC1;
                comm.Parameters.Add("@loc_name", SqlDbType.VarChar).Value = location.strC2;
                comm.Parameters.Add("@loc_addr1", SqlDbType.VarChar).Value = location.strC3;
                comm.Parameters.Add("@loc_addr2", SqlDbType.VarChar).Value = location.strC4;
                comm.Parameters.Add("@loc_city", SqlDbType.VarChar).Value = location.strC5;
                comm.Parameters.Add("@loc_state", SqlDbType.VarChar).Value = location.strC6;
                comm.Parameters.Add("@loc_zip", SqlDbType.VarChar).Value = location.strC7;
                comm.Parameters.Add("@loc_country", SqlDbType.VarChar).Value = location.strC8;
                comm.Parameters.Add("@loc_phone", SqlDbType.VarChar).Value = location.strC9;
                comm.Parameters.Add("@loc_fax", SqlDbType.VarChar).Value = location.strC10;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            else
            {
                comm.CommandText = "update tbl_localidad set loc_id=@loc_id, loc_tco_id=@loc_tco_id, loc_id_interno=@loc_id_interno, loc_name=@loc_name, loc_addr1=@loc_addr1, loc_addr2=@loc_addr2, loc_city=@loc_city, loc_state=@loc_state, loc_zip=@loc_zip, loc_country=@loc_country, loc_phone=@loc_phone, loc_fax=@loc_fax ";
                comm.CommandText += "where loc_id=@loc_id";
                comm.Parameters.Add("@loc_id", SqlDbType.Int).Value = location.intC1;
                comm.Parameters.Add("@loc_tco_id", SqlDbType.Int).Value = 1;
                comm.Parameters.Add("@loc_id_interno", SqlDbType.VarChar).Value = location.strC1;
                comm.Parameters.Add("@loc_name", SqlDbType.VarChar).Value = location.strC2;
                comm.Parameters.Add("@loc_addr1", SqlDbType.VarChar).Value = location.strC3;
                comm.Parameters.Add("@loc_addr2", SqlDbType.VarChar).Value = location.strC4;
                comm.Parameters.Add("@loc_city", SqlDbType.VarChar).Value = location.strC5;
                comm.Parameters.Add("@loc_state", SqlDbType.VarChar).Value = location.strC6;
                comm.Parameters.Add("@loc_zip", SqlDbType.VarChar).Value = location.strC7;
                comm.Parameters.Add("@loc_country", SqlDbType.VarChar).Value = location.strC8;
                comm.Parameters.Add("@loc_phone", SqlDbType.VarChar).Value = location.strC9;
                comm.Parameters.Add("@loc_fax", SqlDbType.VarChar).Value = location.strC10;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            result = true;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static bool InsertAccount(RE_GenericBean account, bool update)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (!update)
            {
                comm.CommandText = "insert into tbl_cuenta (tcu_id, tcu_tco_id, tcu_nombre, tcu_descripcion, tcu_manager, tcu_extraorder) ";
                comm.CommandText += "values ((select COALESCE(max(tcu_id)+1,1) from tbl_cuenta), @tcu_tco_id, @tcu_nombre, @tcu_descripcion, @tcu_manager, @tcu_extraorder)";
                comm.Parameters.Add("@tcu_tco_id", SqlDbType.Int).Value = account.intC2;
                comm.Parameters.Add("@tcu_nombre", SqlDbType.VarChar).Value = account.strC1;
                comm.Parameters.Add("@tcu_descripcion", SqlDbType.VarChar).Value = account.strC2;
                comm.Parameters.Add("@tcu_manager", SqlDbType.VarChar).Value = account.strC3;
                comm.Parameters.Add("@tcu_extraorder", SqlDbType.Decimal).Value = account.decC1;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            } else {
                comm.CommandText = "update tbl_cuenta set tcu_tco_id=@tcu_tco_id, tcu_nombre=@tcu_nombre, tcu_descripcion=@tcu_descripcion, tcu_manager=@tcu_manager, tcu_extraorder=@tcu_extraorder ";
                comm.CommandText += "where tcu_id=@tcu_id";
                comm.Parameters.Add("@tcu_id", SqlDbType.Int).Value = account.intC1;
                comm.Parameters.Add("@tcu_tco_id", SqlDbType.Int).Value = account.intC2;
                comm.Parameters.Add("@tcu_nombre", SqlDbType.VarChar).Value = account.strC1;
                comm.Parameters.Add("@tcu_descripcion", SqlDbType.VarChar).Value = account.strC2;
                comm.Parameters.Add("@tcu_manager", SqlDbType.VarChar).Value = account.strC3;
                comm.Parameters.Add("@tcu_extraorder", SqlDbType.Decimal).Value = account.decC1;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            result = true;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }



    public static bool InsertTypeJob(RE_GenericBean jobposition, bool update)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (!update)
            {
                comm.CommandText = "insert into tbl_tipo_contrato (ttc_id, ttc_tco_id, ttc_nombre, ttc_descripcion) ";
                comm.CommandText += "values ((select COALESCE(max(ttc_id)+1,1) from tbl_tipo_contrato), @ttc_tco_id, @ttc_nombre, @ttc_descripcion)";
                comm.Parameters.Add("@ttc_tco_id", SqlDbType.Int).Value = jobposition.intC2;
                comm.Parameters.Add("@ttc_nombre", SqlDbType.VarChar).Value = jobposition.strC1;
                comm.Parameters.Add("@ttc_descripcion", SqlDbType.VarChar).Value = jobposition.strC2;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            else {
                comm.CommandText = "update tbl_tipo_contrato set ttc_tco_id=@ttc_tco_id, ttc_nombre=@ttc_nombre, ttc_descripcion=@ttc_descripcion ";
                comm.CommandText += "where ttc_id=@ttc_id";
                comm.Parameters.Add("@ttc_id", SqlDbType.Int).Value = jobposition.intC1;
                comm.Parameters.Add("@ttc_tco_id", SqlDbType.Int).Value = jobposition.intC2;
                comm.Parameters.Add("@ttc_nombre", SqlDbType.VarChar).Value = jobposition.strC1;
                comm.Parameters.Add("@ttc_descripcion", SqlDbType.VarChar).Value = jobposition.strC2;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            result = true;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static bool InsertJobPosition(RE_GenericBean puesto, bool update)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (!update)
            {
                comm.CommandText = "insert into tbl_puesto_empleado (tpe_id, tpe_tco_id, tpe_nombre, tpe_descripcion, tpe_salario_minimo, tpe_salario_maximo, tpe_currency, tpe_aumento, tpe_superior) ";
                comm.CommandText += "values ((select COALESCE(max(tpe_id)+1,1) from tbl_puesto_empleado),  @tpe_tco_id, @tpe_nombre, @tpe_descripcion, @tpe_salario_minimo, @tpe_salario_maximo, @tpe_currency, @tpe_aumento, @tpe_superior)";
                comm.Parameters.Add("@tpe_tco_id", SqlDbType.Int).Value = puesto.intC2;
                comm.Parameters.Add("@tpe_nombre", SqlDbType.VarChar).Value = puesto.strC1;
                comm.Parameters.Add("@tpe_descripcion", SqlDbType.VarChar).Value = puesto.strC2;
                comm.Parameters.Add("@tpe_salario_minimo", SqlDbType.Decimal).Value = puesto.decC1;
                comm.Parameters.Add("@tpe_salario_maximo", SqlDbType.Decimal).Value = puesto.decC2;
                comm.Parameters.Add("@tpe_currency", SqlDbType.VarChar).Value = puesto.strC3;
                comm.Parameters.Add("@tpe_aumento", SqlDbType.Decimal).Value = puesto.decC3;
                comm.Parameters.Add("@tpe_superior", SqlDbType.Int).Value = puesto.intC4;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            else
            {
                comm.CommandText = "update tbl_puesto_empleado set tpe_tco_id=@tpe_tco_id, tpe_nombre=@tpe_nombre, tpe_descripcion=@tpe_descripcion, tpe_salario_minimo=@tpe_salario_minimo, tpe_salario_maximo=@tpe_salario_maximo, tpe_currency=@tpe_currency, tpe_aumento=@tpe_aumento, tpe_superior=@tpe_superior ";
                comm.CommandText += "where tpe_id=@tpe_id";
                comm.Parameters.Add("@tpe_id", SqlDbType.Int).Value = puesto.intC1;
                comm.Parameters.Add("@tpe_tco_id", SqlDbType.Int).Value = puesto.intC2;
                comm.Parameters.Add("@tpe_nombre", SqlDbType.VarChar).Value = puesto.strC1;
                comm.Parameters.Add("@tpe_descripcion", SqlDbType.VarChar).Value = puesto.strC2;
                comm.Parameters.Add("@tpe_salario_minimo", SqlDbType.Decimal).Value = puesto.decC1;
                comm.Parameters.Add("@tpe_salario_maximo", SqlDbType.Decimal).Value = puesto.decC2;
                comm.Parameters.Add("@tpe_currency", SqlDbType.VarChar).Value = puesto.strC3;
                comm.Parameters.Add("@tpe_aumento", SqlDbType.Decimal).Value = puesto.decC3;
                comm.Parameters.Add("@tpe_superior", SqlDbType.Int).Value = puesto.intC4;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            result = true;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_GenericBean InsertaPersonalInfo(RE_GenericBean personalinf, RE_GenericBean contact1, RE_GenericBean contact2, RE_GenericBean emergency1, RE_GenericBean emergency2, RE_GenericBean jobposition, RE_GenericBean salary, bool update)
    {
        RE_GenericBean result = null;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        int per_id = 0;
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (!update)
            {
                comm.CommandText = "select COALESCE(max(tper_id)+1,1) from tbl_persona";
                per_id=int.Parse(comm.ExecuteScalar().ToString());
                comm.Parameters.Clear();

                comm.CommandText = "insert into tbl_persona (tper_id, tper_nombre, tper_apellido, tper_apellido_casada, tper_genero, tper_estado_civil, tper_fecha_nacimiento, tper_nit, tper_igss, tper_idper) ";
                comm.CommandText += "values (@tper_id,  @tper_nombre, @tper_apellido, @tper_apellido_casada, @tper_genero, @tper_estado_civil, @tper_fecha_nacimiento, @tper_nit, @tper_igss, @tper_idper)";
                comm.Parameters.Add("@tper_id", SqlDbType.Int).Value = per_id;
                comm.Parameters.Add("@tper_nombre", SqlDbType.VarChar).Value = personalinf.strC1;
                comm.Parameters.Add("@tper_apellido", SqlDbType.VarChar).Value = personalinf.strC2;
                comm.Parameters.Add("@tper_apellido_casada", SqlDbType.VarChar).Value = personalinf.strC3;
                comm.Parameters.Add("@tper_nit", SqlDbType.VarChar).Value = personalinf.strC4;
                comm.Parameters.Add("@tper_genero", SqlDbType.VarChar).Value = personalinf.strC5;
                comm.Parameters.Add("@tper_estado_civil", SqlDbType.VarChar).Value = personalinf.strC6;
                if (personalinf.strC7.Equals("")) personalinf.strC7 = "1/1/1900";
                comm.Parameters.Add("@tper_fecha_nacimiento", SqlDbType.Date).Value = personalinf.strC7;
                
                comm.Parameters.Add("@tper_igss", SqlDbType.VarChar).Value = personalinf.strC8;
                comm.Parameters.Add("@tper_idper", SqlDbType.VarChar).Value = personalinf.strC9;
               
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                if (!personalinf.strC10.Equals("")) {
                    comm.CommandText = "update  tbl_persona  set tper_foto = @tper_foto) where tper_id=@tper_id ";
                    comm.Parameters.Add("@tper_foto", SqlDbType.VarChar).Value = personalinf.strC10;
                    comm.Parameters.Add("@tper_id", SqlDbType.Int).Value = per_id;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
 
                }

            }
            else
            {
                per_id=personalinf.intC1;
                comm.CommandText = "update tbl_persona set tper_nombre=@tper_nombre, tper_apellido=@tper_apellido, tper_apellido_casada=@tper_apellido_casada, tper_genero=@tper_genero, tper_estado_civil=@tper_estado_civil, tper_fecha_nacimiento=@tper_fecha_nacimiento, tper_nit=@tper_nit, tper_igss=@tper_igss , @tper_idper=@tper_idper";
                comm.CommandText += " where tper_id=@tper_id";
                comm.Parameters.Add("@tper_id", SqlDbType.Int).Value = per_id;
                comm.Parameters.Add("@tper_nombre", SqlDbType.VarChar).Value = personalinf.strC1;
                comm.Parameters.Add("@tper_apellido", SqlDbType.VarChar).Value = personalinf.strC2;
                comm.Parameters.Add("@tper_apellido_casada", SqlDbType.VarChar).Value = personalinf.strC3;
                comm.Parameters.Add("@tper_genero", SqlDbType.VarChar).Value = personalinf.strC5;
                comm.Parameters.Add("@tper_estado_civil", SqlDbType.VarChar).Value = personalinf.strC6;
                comm.Parameters.Add("@tper_fecha_nacimiento", SqlDbType.Date).Value = personalinf.strC7;
                comm.Parameters.Add("@tper_nit", SqlDbType.VarChar).Value = personalinf.strC4;
                comm.Parameters.Add("@tper_igss", SqlDbType.VarChar).Value = personalinf.strC8;
                comm.Parameters.Add("@tper_idper", SqlDbType.VarChar).Value = personalinf.strC9;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                per_id = personalinf.intC1;


                if (!personalinf.strC10.Equals(""))
                {
                    comm.CommandText = "update  tbl_persona  set tper_foto = @tper_foto where tper_id=@tper_id ";
                    comm.Parameters.Add("@tper_foto", SqlDbType.VarChar).Value = personalinf.strC10;
                    comm.Parameters.Add("@tper_id", SqlDbType.Int).Value = per_id;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();

                }

            }

            //direciones
            if (contact1 != null) {
                if (contact1.intC1 == 0)
                {
                    comm.CommandText = "insert into tbl_persona_direccion (tpd_id, tpd_tper_id, tpd_direccion1, tpd_direccion2, tpd_ciudad, tpd_state, tpd_zip, tpd_country, tpd_telefono1, tpd_telefono2, tpd_telefono3, tpd_work_email, tpd_personal_email)";
                    comm.CommandText += "values ((select COALESCE(max(tpd_id)+1,1) from tbl_persona_direccion),  @tpd_tper_id, @tpd_direccion1, @tpd_direccion2, @tpd_ciudad, @tpd_state, @tpd_zip, @tpd_country, @tpd_telefono1, @tpd_telefono2, @tpd_telefono3, @tpd_work_email, @tpd_personal_email)";
                    comm.Parameters.Add("@tpd_tper_id", SqlDbType.Int).Value = per_id;
                    comm.Parameters.Add("@tpd_direccion1", SqlDbType.VarChar).Value = contact1.strC1;
                    comm.Parameters.Add("@tpd_direccion2", SqlDbType.VarChar).Value = contact1.strC2;
                    comm.Parameters.Add("@tpd_ciudad", SqlDbType.VarChar).Value = contact1.strC3;
                    comm.Parameters.Add("@tpd_state", SqlDbType.VarChar).Value = contact1.strC4;
                    comm.Parameters.Add("@tpd_zip", SqlDbType.VarChar).Value = contact1.strC5;
                    comm.Parameters.Add("@tpd_country", SqlDbType.VarChar).Value = contact1.strC6;
                    comm.Parameters.Add("@tpd_telefono1", SqlDbType.VarChar).Value = contact1.strC7;
                    comm.Parameters.Add("@tpd_telefono2", SqlDbType.VarChar).Value = contact1.strC8;
                    comm.Parameters.Add("@tpd_telefono3", SqlDbType.VarChar).Value = contact1.strC11;
                    comm.Parameters.Add("@tpd_work_email", SqlDbType.VarChar).Value = contact1.strC9;
                    comm.Parameters.Add("@tpd_personal_email", SqlDbType.VarChar).Value = contact1.strC10;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }
                else {
                    comm.CommandText = "update tbl_persona_direccion set tpd_tper_id=@tpd_tper_id, tpd_direccion1=@tpd_direccion1, tpd_direccion2=@tpd_direccion2, tpd_ciudad=@tpd_ciudad, tpd_state=@tpd_state, tpd_zip=@tpd_zip, tpd_country=@tpd_country, tpd_telefono1=@tpd_telefono1, tpd_telefono2=@tpd_telefono2, tpd_telefono3=@tpd_telefono3, tpd_work_email=@tpd_work_email, tpd_personal_email=@tpd_personal_email";
                    comm.CommandText += " where tpd_id=@tpd_id";
                    comm.Parameters.Add("@tpd_id", SqlDbType.Int).Value = contact1.intC1;
                    comm.Parameters.Add("@tpd_tper_id", SqlDbType.Int).Value = per_id;
                    comm.Parameters.Add("@tpd_direccion1", SqlDbType.VarChar).Value = contact1.strC1;
                    comm.Parameters.Add("@tpd_direccion2", SqlDbType.VarChar).Value = contact1.strC2;
                    comm.Parameters.Add("@tpd_ciudad", SqlDbType.VarChar).Value = contact1.strC3;
                    comm.Parameters.Add("@tpd_state", SqlDbType.VarChar).Value = contact1.strC4;
                    comm.Parameters.Add("@tpd_zip", SqlDbType.VarChar).Value = contact1.strC5;
                    comm.Parameters.Add("@tpd_country", SqlDbType.VarChar).Value = contact1.strC6;
                    comm.Parameters.Add("@tpd_telefono1", SqlDbType.VarChar).Value = contact1.strC7;
                    comm.Parameters.Add("@tpd_telefono2", SqlDbType.VarChar).Value = contact1.strC8;
                    comm.Parameters.Add("@tpd_telefono3", SqlDbType.VarChar).Value = contact1.strC11;
                    comm.Parameters.Add("@tpd_work_email", SqlDbType.VarChar).Value = contact1.strC9;
                    comm.Parameters.Add("@tpd_personal_email", SqlDbType.VarChar).Value = contact1.strC10;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }
            }

            if (contact2 != null)
            {
                if (contact2.intC1 == 0)
                {
                    comm.CommandText = "insert into tbl_persona_direccion (tpd_id, tpd_tper_id, tpd_direccion1, tpd_direccion2, tpd_ciudad, tpd_state, tpd_zip, tpd_country, tpd_telefono1, tpd_telefono2, tpd_telefono3, tpd_work_email, tpd_personal_email)";
                    comm.CommandText += "values ((select COALESCE(max(tpd_id)+1,1) from tbl_persona_direccion),  @tpd_tper_id, @tpd_direccion1, @tpd_direccion2, @tpd_ciudad, @tpd_state, @tpd_zip, @tpd_country, @tpd_telefono1, @tpd_telefono2, @tpd_telefono3, @tpd_work_email, @tpd_personal_email)";
                    comm.Parameters.Add("@tpd_tper_id", SqlDbType.Int).Value = per_id;
                    comm.Parameters.Add("@tpd_direccion1", SqlDbType.VarChar).Value = contact2.strC1;
                    comm.Parameters.Add("@tpd_direccion2", SqlDbType.VarChar).Value = contact2.strC2;
                    comm.Parameters.Add("@tpd_ciudad", SqlDbType.VarChar).Value = contact2.strC3;
                    comm.Parameters.Add("@tpd_state", SqlDbType.VarChar).Value = contact2.strC4;
                    comm.Parameters.Add("@tpd_zip", SqlDbType.VarChar).Value = contact2.strC5;
                    comm.Parameters.Add("@tpd_country", SqlDbType.VarChar).Value = contact2.strC6;
                    comm.Parameters.Add("@tpd_telefono1", SqlDbType.VarChar).Value = contact2.strC7;
                    comm.Parameters.Add("@tpd_telefono2", SqlDbType.VarChar).Value = contact2.strC8;
                    comm.Parameters.Add("@tpd_telefono3", SqlDbType.VarChar).Value = contact2.strC11;
                    comm.Parameters.Add("@tpd_work_email", SqlDbType.VarChar).Value = contact2.strC9;
                    comm.Parameters.Add("@tpd_personal_email", SqlDbType.VarChar).Value = contact2.strC10;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }
                else
                {
                    comm.CommandText = "update tbl_persona_direccion set tpd_tper_id=@tpd_tper_id, tpd_direccion1=@tpd_direccion1, tpd_direccion2=@tpd_direccion2, tpd_ciudad=@tpd_ciudad, tpd_state=@tpd_state, tpd_zip=@tpd_zip, tpd_country=@tpd_country, tpd_telefono1=@tpd_telefono1, tpd_telefono2=@tpd_telefono2, tpd_telefono3=@tpd_telefono3, tpd_work_email=@tpd_work_email, tpd_personal_email=@tpd_personal_email";
                    comm.CommandText += " where tpd_id=@tpd_id";
                    comm.Parameters.Add("@tpd_id", SqlDbType.Int).Value = contact2.intC1;
                    comm.Parameters.Add("@tpd_tper_id", SqlDbType.Int).Value = per_id;
                    comm.Parameters.Add("@tpd_direccion1", SqlDbType.VarChar).Value = contact2.strC1;
                    comm.Parameters.Add("@tpd_direccion2", SqlDbType.VarChar).Value = contact2.strC2;
                    comm.Parameters.Add("@tpd_ciudad", SqlDbType.VarChar).Value = contact2.strC3;
                    comm.Parameters.Add("@tpd_state", SqlDbType.VarChar).Value = contact2.strC4;
                    comm.Parameters.Add("@tpd_zip", SqlDbType.VarChar).Value = contact2.strC5;
                    comm.Parameters.Add("@tpd_country", SqlDbType.VarChar).Value = contact2.strC6;
                    comm.Parameters.Add("@tpd_telefono1", SqlDbType.VarChar).Value = contact2.strC7;
                    comm.Parameters.Add("@tpd_telefono2", SqlDbType.VarChar).Value = contact2.strC8;
                    comm.Parameters.Add("@tpd_telefono3", SqlDbType.VarChar).Value = contact2.strC11;
                    comm.Parameters.Add("@tpd_work_email", SqlDbType.VarChar).Value = contact2.strC9;
                    comm.Parameters.Add("@tpd_personal_email", SqlDbType.VarChar).Value = contact2.strC10;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }
            }

            // datos de emergencia .............................
            if (emergency1 != null) {
                if (emergency1.intC1 == 0) {
                    comm.CommandText = "insert into tbl_persona_emergencia(tpem_id, tpem_tper_id, tpem_nombre, tpem_relacion, tpem_direccion1, tpem_direccion2, tpem_ciudad, tpem_state, tpem_zip, tpem_country, tpem_telefono1, tpem_telefono2, tpem_telefono3, tpem_email)";
                    comm.CommandText += "values ((select COALESCE(max(tpem_id)+1,1) from tbl_persona_emergencia),  @tpem_tper_id, @tpem_nombre, @tpem_relacion, @tpem_direccion1, @tpem_direccion2, @tpem_ciudad, @tpem_state, @tpem_zip, @tpem_country, @tpem_telefono1, @tpem_telefono2, @tpem_telefono3, @tpem_email)";
                    comm.Parameters.Add("@tpem_tper_id", SqlDbType.Int).Value = per_id;
                    comm.Parameters.Add("@tpem_nombre", SqlDbType.VarChar).Value = emergency1.strC1;
                    comm.Parameters.Add("@tpem_relacion", SqlDbType.VarChar).Value = emergency1.strC2;
                    comm.Parameters.Add("@tpem_direccion1", SqlDbType.VarChar).Value = emergency1.strC3;
                    comm.Parameters.Add("@tpem_direccion2", SqlDbType.VarChar).Value = emergency1.strC4;
                    comm.Parameters.Add("@tpem_ciudad", SqlDbType.VarChar).Value = emergency1.strC5;
                    comm.Parameters.Add("@tpem_state", SqlDbType.VarChar).Value = emergency1.strC6;
                    comm.Parameters.Add("@tpem_zip", SqlDbType.VarChar).Value = emergency1.strC7;
                    comm.Parameters.Add("@tpem_country", SqlDbType.VarChar).Value = emergency1.strC8;
                    comm.Parameters.Add("@tpem_telefono1", SqlDbType.VarChar).Value = emergency1.strC9;
                    comm.Parameters.Add("@tpem_telefono2", SqlDbType.VarChar).Value = emergency1.strC11;
                    comm.Parameters.Add("@tpem_telefono3", SqlDbType.VarChar).Value = emergency1.strC12;
                    comm.Parameters.Add("@tpem_email", SqlDbType.VarChar).Value = emergency1.strC10;

                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }
                else
                {
                    comm.CommandText = "update tbl_persona_emergencia set tpem_tper_id=@tpem_tper_id, tpem_nombre=@tpem_nombre, tpem_relacion=@tpem_relacion, tpem_direccion1=@tpem_direccion1, tpem_direccion2=@tpem_direccion2, tpem_ciudad=@tpem_ciudad, tpem_state=@tpem_state, tpem_zip=@tpem_zip, tpem_country=@tpem_country, tpem_telefono1=@tpem_telefono1, tpem_telefono2=@tpem_telefono2, tpem_telefono3=@tpem_telefono3, tpem_email=@tpem_email";
                    comm.CommandText += " where tpem_id=@tpem_id";
                    comm.Parameters.Add("@tpem_id", SqlDbType.Int).Value = emergency1.intC1;
                    comm.Parameters.Add("@tpem_tper_id", SqlDbType.Int).Value = per_id;
                    comm.Parameters.Add("@tpem_nombre", SqlDbType.VarChar).Value = emergency1.strC1;
                    comm.Parameters.Add("@tpem_relacion", SqlDbType.VarChar).Value = emergency1.strC2;
                    comm.Parameters.Add("@tpem_direccion1", SqlDbType.VarChar).Value = emergency1.strC3;
                    comm.Parameters.Add("@tpem_direccion2", SqlDbType.VarChar).Value = emergency1.strC4;
                    comm.Parameters.Add("@tpem_ciudad", SqlDbType.VarChar).Value = emergency1.strC5;
                    comm.Parameters.Add("@tpem_state", SqlDbType.VarChar).Value = emergency1.strC6;
                    comm.Parameters.Add("@tpem_zip", SqlDbType.VarChar).Value = emergency1.strC7;
                    comm.Parameters.Add("@tpem_country", SqlDbType.VarChar).Value = emergency1.strC8;
                    comm.Parameters.Add("@tpem_telefono1", SqlDbType.VarChar).Value = emergency1.strC9;
                    comm.Parameters.Add("@tpem_telefono2", SqlDbType.VarChar).Value = emergency1.strC11;
                    comm.Parameters.Add("@tpem_telefono3", SqlDbType.VarChar).Value = emergency1.strC12;
                    comm.Parameters.Add("@tpem_email", SqlDbType.VarChar).Value = emergency1.strC10;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }

                if (emergency2 != null)
                {
                    if (emergency2.intC1 == 0)
                    {
                        comm.CommandText = "insert into tbl_persona_emergencia(tpem_id, tpem_tper_id, tpem_nombre, tpem_relacion, tpem_direccion1, tpem_direccion2, tpem_ciudad, tpem_state, tpem_zip, tpem_country, tpem_telefono1, tpem_telefono2, tpem_telefono3, tpem_email)";
                        comm.CommandText += "values ((select COALESCE(max(tpem_id)+1,1) from tbl_persona_emergencia),  @tpem_tper_id, @tpem_nombre, @tpem_relacion, @tpem_direccion1, @tpem_direccion2, @tpem_ciudad, @tpem_state, @tpem_zip, @tpem_country, @tpem_telefono1, @tpem_telefono2, @tpem_telefono3, @tpem_email)";
                        comm.Parameters.Add("@tpem_tper_id", SqlDbType.Int).Value = per_id;
                        comm.Parameters.Add("@tpem_nombre", SqlDbType.VarChar).Value = emergency2.strC1;
                        comm.Parameters.Add("@tpem_relacion", SqlDbType.VarChar).Value = emergency2.strC2;
                        comm.Parameters.Add("@tpem_direccion1", SqlDbType.VarChar).Value = emergency2.strC3;
                        comm.Parameters.Add("@tpem_direccion2", SqlDbType.VarChar).Value = emergency2.strC4;
                        comm.Parameters.Add("@tpem_ciudad", SqlDbType.VarChar).Value = emergency2.strC5;
                        comm.Parameters.Add("@tpem_state", SqlDbType.VarChar).Value = emergency2.strC6;
                        comm.Parameters.Add("@tpem_zip", SqlDbType.VarChar).Value = emergency2.strC7;
                        comm.Parameters.Add("@tpem_country", SqlDbType.VarChar).Value = emergency2.strC8;
                        comm.Parameters.Add("@tpem_telefono1", SqlDbType.VarChar).Value = emergency2.strC9;
                        comm.Parameters.Add("@tpem_telefono2", SqlDbType.VarChar).Value = emergency2.strC11;
                        comm.Parameters.Add("@tpem_telefono3", SqlDbType.VarChar).Value = emergency2.strC12;
                        comm.Parameters.Add("@tpem_email", SqlDbType.VarChar).Value = emergency2.strC10;

                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();
                    }
                    else
                    {
                        comm.CommandText = "update tbl_persona_emergencia set tpem_tper_id=@tpem_tper_id, tpem_nombre=@tpem_nombre, tpem_relacion=@tpem_relacion, tpem_direccion1=@tpem_direccion1, tpem_direccion2=@tpem_direccion2, tpem_ciudad=@tpem_ciudad, tpem_state=@tpem_state, tpem_zip=@tpem_zip, tpem_country=@tpem_country, tpem_telefono1=@tpem_telefono1, tpem_telefono2=@tpem_telefono2, tpem_telefono3=@tpem_telefono3, tpem_email=@tpem_email";
                        comm.CommandText += " where tpem_id=@tpem_id";
                        comm.Parameters.Add("@tpem_id", SqlDbType.Int).Value = emergency1.intC1;
                        comm.Parameters.Add("@tpem_tper_id", SqlDbType.Int).Value = per_id;
                        comm.Parameters.Add("@tpem_nombre", SqlDbType.VarChar).Value = emergency1.strC1;
                        comm.Parameters.Add("@tpem_relacion", SqlDbType.VarChar).Value = emergency1.strC2;
                        comm.Parameters.Add("@tpem_direccion1", SqlDbType.VarChar).Value = emergency1.strC3;
                        comm.Parameters.Add("@tpem_direccion2", SqlDbType.VarChar).Value = emergency1.strC4;
                        comm.Parameters.Add("@tpem_ciudad", SqlDbType.VarChar).Value = emergency1.strC5;
                        comm.Parameters.Add("@tpem_state", SqlDbType.VarChar).Value = emergency1.strC6;
                        comm.Parameters.Add("@tpem_zip", SqlDbType.VarChar).Value = emergency1.strC7;
                        comm.Parameters.Add("@tpem_country", SqlDbType.VarChar).Value = emergency1.strC8;
                        comm.Parameters.Add("@tpem_telefono1", SqlDbType.VarChar).Value = emergency1.strC9;
                        comm.Parameters.Add("@tpem_telefono2", SqlDbType.VarChar).Value = emergency1.strC11;
                        comm.Parameters.Add("@tpem_telefono3", SqlDbType.VarChar).Value = emergency1.strC12;
                        comm.Parameters.Add("@tpem_email", SqlDbType.VarChar).Value = emergency1.strC10;
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();
                    }
                }
            }
            

            // obtengo el id del empleado para actualizar o bien insertar si no existe. enra id persona y id cuenta
            int emp_id=getIDEmpleadoCuenta(per_id, jobposition.intC4);

            if (emp_id == 0)
            {
                comm.CommandText = "select COALESCE(max(emp_id)+1,1) from tbl_empleado";
                emp_id = int.Parse(comm.ExecuteScalar().ToString());
                comm.Parameters.Clear();

                comm.CommandText = "insert into tbl_empleado (emp_id, emp_tper_id, emp_tcu_id, emp_codigo_empleado, emp_xml, emp_hired_date, emp_end_date) ";
                comm.CommandText += "values (@emp_id, @emp_tper_id, @emp_tcu_id, @emp_codigo_empleado, @emp_xml, @emp_hired_date, @emp_end_date)";
                comm.Parameters.Add("@emp_id", SqlDbType.Int).Value = emp_id;
                comm.Parameters.Add("@emp_tper_id", SqlDbType.Int).Value = per_id;
                comm.Parameters.Add("@emp_tcu_id", SqlDbType.Int).Value = jobposition.intC4;
                comm.Parameters.Add("@emp_codigo_empleado", SqlDbType.VarChar).Value = jobposition.strC6;
                comm.Parameters.Add("@emp_xml", SqlDbType.Xml).Value = "";
                if (jobposition.strC1.Equals("")) jobposition.strC1 = "1/1/1900";
                if (jobposition.strC2.Equals("")) jobposition.strC2 = "1/1/1900";
                comm.Parameters.Add("@emp_hired_date", SqlDbType.VarChar).Value = jobposition.strC1;
                comm.Parameters.Add("@emp_end_date", SqlDbType.VarChar).Value = jobposition.strC2;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                //seccion de expediente
                comm.CommandText = "update tbl_expediente set tex_fecha_fin=@tex_fecha_fin where tex_fecha_fin is null and tex_emp_id=@tex_emp_id";
                comm.Parameters.Add("@tex_emp_id", SqlDbType.Int).Value = emp_id;
                comm.Parameters.Add("@tex_fecha_fin", SqlDbType.VarChar).Value = jobposition.strC2;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = "insert into tbl_expediente (tex_id, tex_emp_id, tex_tpe_id, tex_ttc_id, tex_status, tex_fecha_inicio, tex_salario, tex_bonificacion, tex_commission, tex_other_commission, tex_currency) ";
                comm.CommandText += "values ((select COALESCE(max(tex_id)+1,1) from tbl_expediente), @tex_emp_id, @tex_tpe_id, @tex_ttc_id, @tex_status, @tex_fecha_inicio, @tex_salario, @tex_bonificacion, @tex_commission, @tex_other_commission, @tex_currency)";
                comm.Parameters.Add("@tex_emp_id", SqlDbType.Int).Value = emp_id;
                comm.Parameters.Add("@tex_tpe_id", SqlDbType.Int).Value = jobposition.intC1;
                comm.Parameters.Add("@tex_ttc_id", SqlDbType.Int).Value = salary.intC2;
                comm.Parameters.Add("@tex_status", SqlDbType.Int).Value = jobposition.intC2;
                comm.Parameters.Add("@tex_fecha_inicio", SqlDbType.VarChar).Value = jobposition.strC3;
                comm.Parameters.Add("@tex_salario", SqlDbType.Decimal).Value = salary.decC1;
                comm.Parameters.Add("@tex_bonificacion", SqlDbType.Decimal).Value = salary.decC2;
                comm.Parameters.Add("@tex_commission", SqlDbType.Decimal).Value = salary.decC3;
                comm.Parameters.Add("@tex_other_commission", SqlDbType.Decimal).Value = salary.decC4;
                comm.Parameters.Add("@tex_currency", SqlDbType.VarChar).Value = salary.strC1;
                
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                //fin seccion de expediente
            } else {
                comm.CommandText = "update tbl_empleado set emp_tper_id=@emp_tper_id, emp_tcu_id=@emp_tcu_id, emp_codigo_empleado=@emp_codigo_empleado, emp_xml=@emp_xml, emp_hired_date=@emp_hired_date, emp_end_date=@emp_end_date ";
                comm.CommandText += " where emp_id=@emp_id";
                comm.Parameters.Add("@emp_id", SqlDbType.Int).Value = emp_id;
                comm.Parameters.Add("@emp_tper_id", SqlDbType.Int).Value = per_id;
                comm.Parameters.Add("@emp_tcu_id", SqlDbType.Int).Value = jobposition.intC4;
                comm.Parameters.Add("@emp_codigo_empleado", SqlDbType.VarChar).Value = jobposition.strC6;
                comm.Parameters.Add("@emp_xml", SqlDbType.Xml).Value = "";
                if (jobposition.strC1.Equals("")) jobposition.strC1 = "1/1/1900";
                if (jobposition.strC2.Equals("")) jobposition.strC2 = "1/1/1900";
                comm.Parameters.Add("@emp_hired_date", SqlDbType.VarChar).Value = jobposition.strC1;
                comm.Parameters.Add("@emp_end_date", SqlDbType.VarChar).Value = jobposition.strC2;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                //seccion de expediente
                
                comm.CommandText = "update tbl_expediente set tex_fecha_fin=@tex_fecha_fin where tex_fecha_fin is null and tex_emp_id=@tex_emp_id";
                comm.Parameters.Add("@tex_emp_id", SqlDbType.Int).Value = emp_id;
                comm.Parameters.Add("@tex_fecha_fin", SqlDbType.VarChar).Value = jobposition.strC2;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = "insert into tbl_expediente (tex_id, tex_emp_id, tex_tpe_id, tex_ttc_id, tex_status, tex_fecha_inicio, tex_salario, tex_bonificacion, tex_commission, tex_other_commission, tex_currency) ";
                comm.CommandText += "values ((select COALESCE(max(tex_id)+1,1) from tbl_expediente), @tex_emp_id, @tex_tpe_id, @tex_ttc_id, @tex_status, @tex_fecha_inicio, @tex_salario, @tex_bonificacion, @tex_commission, @tex_other_commission, @tex_currency)";
                comm.Parameters.Add("@tex_emp_id", SqlDbType.Int).Value = emp_id;
                comm.Parameters.Add("@tex_tpe_id", SqlDbType.Int).Value = jobposition.intC1;
                comm.Parameters.Add("@tex_ttc_id", SqlDbType.Int).Value = salary.intC2;
                comm.Parameters.Add("@tex_status", SqlDbType.Int).Value = jobposition.intC2;
                
                comm.Parameters.Add("@tex_fecha_inicio", SqlDbType.VarChar).Value = jobposition.strC3;
                comm.Parameters.Add("@tex_salario", SqlDbType.Decimal).Value = salary.decC1;
                comm.Parameters.Add("@tex_bonificacion", SqlDbType.Decimal).Value = salary.decC2;
                comm.Parameters.Add("@tex_commission", SqlDbType.Decimal).Value = salary.decC3;
                comm.Parameters.Add("@tex_other_commission", SqlDbType.Decimal).Value = salary.decC4;
                comm.Parameters.Add("@tex_currency", SqlDbType.VarChar).Value = salary.strC1;
                
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                //fin seccion de expediente

            }

            result = new RE_GenericBean();
            result.intC1 = emp_id;
            result.intC2 = per_id;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log("Fallo el ingreso de persona:  "+e.Message.ToString()+e.StackTrace.ToString());
            return null;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_GenericBean InsertaPersonalInfoPer(RE_GenericBean personalinf, RE_GenericBean contact1, bool update)
    {
        RE_GenericBean result = null;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        int per_id = 0; int emp_id = 0;
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (!update)
            {
                comm.CommandText = "select COALESCE(max(tper_id)+1,1) from tbl_persona";
                per_id = int.Parse(comm.ExecuteScalar().ToString());
                comm.Parameters.Clear();

                comm.CommandText = "insert into tbl_persona (tper_id,  tper_nombre,tper_nombre2 ,tper_apellido, tper_apellido2,tper_apellido_casada, tper_genero, tper_estado_civil, tper_fecha_nacimiento) ";
                comm.CommandText += "values (@tper_id,  @tper_nombre,@tper_nombre2, @tper_apellido, @tper_apellido2,@tper_apellido_casada, @tper_genero, @tper_estado_civil, @tper_fecha_nacimiento )";
                comm.Parameters.Add("@tper_id", SqlDbType.Int).Value = per_id;
               
                comm.Parameters.Add("@tper_nombre", SqlDbType.VarChar).Value = personalinf.strC2;
                comm.Parameters.Add("@tper_nombre2", SqlDbType.VarChar).Value = personalinf.strC3;
                comm.Parameters.Add("@tper_apellido", SqlDbType.VarChar).Value = personalinf.strC4;
                comm.Parameters.Add("@tper_apellido2", SqlDbType.VarChar).Value = personalinf.strC5;
                comm.Parameters.Add("@tper_apellido_casada", SqlDbType.VarChar).Value = personalinf.strC6;
                comm.Parameters.Add("@tper_genero", SqlDbType.VarChar).Value = personalinf.strC7;
                comm.Parameters.Add("@tper_estado_civil", SqlDbType.VarChar).Value = personalinf.strC8;
                if (personalinf.strC7.Equals("")) personalinf.strC8 = "1/1/1900";
                comm.Parameters.Add("@tper_fecha_nacimiento", SqlDbType.Date).Value = personalinf.strC9;
                
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = "select COALESCE(max(emp_id)+1,1) from tbl_empleado";
                emp_id = int.Parse(comm.ExecuteScalar().ToString());
                comm.Parameters.Clear();

                comm.CommandText = "insert into tbl_empleado (emp_id, emp_tper_id, emp_tcu_id, emp_codigo_empleado) ";
                comm.CommandText += "values (@emp_id, @emp_tper_id, @emp_tcu_id,0)";
                comm.Parameters.Add("@emp_id", SqlDbType.Int).Value = emp_id;
                comm.Parameters.Add("@emp_tper_id", SqlDbType.Int).Value = per_id;
                comm.Parameters.Add("@emp_tcu_id", SqlDbType.Int).Value = personalinf.intC3;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = "insert into tbl_expediente (tex_id, tex_emp_id, tex_status) ";
                comm.CommandText += "values ((select COALESCE(max(emp_id)+1,1) from tbl_empleado), @emp_id,1)";
                comm.Parameters.Add("@emp_id", SqlDbType.Int).Value = emp_id;
                comm.Parameters.Add("@emp_tper_id", SqlDbType.Int).Value = per_id;
                comm.Parameters.Add("@emp_tcu_id", SqlDbType.Int).Value = personalinf.intC3;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();



            }
            else
            {
                per_id = personalinf.intC1;
                comm.CommandText = "update tbl_persona set   tper_nombre=@tper_nombre,  tper_nombre2=@tper_nombre2, tper_apellido=@tper_apellido, tper_apellido2=@tper_apellido2, tper_apellido_casada=@tper_apellido_casada, tper_genero=@tper_genero, tper_estado_civil=@tper_estado_civil, tper_fecha_nacimiento=@tper_fecha_nacimiento ";
                comm.CommandText += " where tper_id=@tper_id";
                comm.Parameters.Add("@tper_id", SqlDbType.Int).Value = per_id;
               
                comm.Parameters.Add("@tper_nombre", SqlDbType.VarChar).Value = personalinf.strC2;
                comm.Parameters.Add("@tper_nombre2", SqlDbType.VarChar).Value = personalinf.strC3;
                comm.Parameters.Add("@tper_apellido", SqlDbType.VarChar).Value = personalinf.strC4;
                comm.Parameters.Add("@tper_apellido2", SqlDbType.VarChar).Value = personalinf.strC5;
                comm.Parameters.Add("@tper_apellido_casada", SqlDbType.VarChar).Value = personalinf.strC6;
                comm.Parameters.Add("@tper_genero", SqlDbType.VarChar).Value = personalinf.strC7;
                comm.Parameters.Add("@tper_estado_civil", SqlDbType.VarChar).Value = personalinf.strC8;
                comm.Parameters.Add("@tper_fecha_nacimiento", SqlDbType.Date).Value = personalinf.strC9;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();


                emp_id = personalinf.intC2;
                

                
            }

            //direciones
            if (contact1 != null)
            {
                if (contact1.intC1 == 0)
                {
                    comm.CommandText = "insert into tbl_persona_direccion (tpd_id, tpd_tper_id, tpd_direccion1, tpd_direccion2, tpd_ciudad, tpd_state, tpd_zip, tpd_country, tpd_telefono1, tpd_telefono2, tpd_telefono3, tpd_work_email, tpd_personal_email)";
                    comm.CommandText += "values ((select COALESCE(max(tpd_id)+1,1) from tbl_persona_direccion),  @tpd_tper_id, @tpd_direccion1, @tpd_direccion2, @tpd_ciudad, @tpd_state, @tpd_zip, @tpd_country, @tpd_telefono1, @tpd_telefono2, @tpd_telefono3, @tpd_work_email, @tpd_personal_email)";
                    comm.Parameters.Add("@tpd_tper_id", SqlDbType.Int).Value = per_id;
                    comm.Parameters.Add("@tpd_direccion1", SqlDbType.VarChar).Value = contact1.strC1;
                    comm.Parameters.Add("@tpd_direccion2", SqlDbType.VarChar).Value = contact1.strC2;
                    comm.Parameters.Add("@tpd_ciudad", SqlDbType.VarChar).Value = contact1.strC3;
                    comm.Parameters.Add("@tpd_state", SqlDbType.VarChar).Value = contact1.strC4;
                    comm.Parameters.Add("@tpd_zip", SqlDbType.VarChar).Value = contact1.strC5;
                    comm.Parameters.Add("@tpd_country", SqlDbType.VarChar).Value = contact1.strC6;
                    comm.Parameters.Add("@tpd_telefono1", SqlDbType.VarChar).Value = contact1.strC7;
                    comm.Parameters.Add("@tpd_telefono2", SqlDbType.VarChar).Value = contact1.strC8;
                    comm.Parameters.Add("@tpd_telefono3", SqlDbType.VarChar).Value = contact1.strC11;
                    comm.Parameters.Add("@tpd_work_email", SqlDbType.VarChar).Value = contact1.strC9;
                    comm.Parameters.Add("@tpd_personal_email", SqlDbType.VarChar).Value = contact1.strC10;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }
                else
                {
                    comm.CommandText = "update tbl_persona_direccion set tpd_tper_id=@tpd_tper_id, tpd_direccion1=@tpd_direccion1, tpd_direccion2=@tpd_direccion2, tpd_ciudad=@tpd_ciudad, tpd_state=@tpd_state, tpd_zip=@tpd_zip, tpd_country=@tpd_country, tpd_telefono1=@tpd_telefono1, tpd_telefono2=@tpd_telefono2, tpd_telefono3=@tpd_telefono3, tpd_work_email=@tpd_work_email, tpd_personal_email=@tpd_personal_email";
                    comm.CommandText += " where tpd_id=@tpd_id";
                    comm.Parameters.Add("@tpd_id", SqlDbType.Int).Value = contact1.intC1;
                    comm.Parameters.Add("@tpd_tper_id", SqlDbType.Int).Value = per_id;
                    comm.Parameters.Add("@tpd_direccion1", SqlDbType.VarChar).Value = contact1.strC1;
                    comm.Parameters.Add("@tpd_direccion2", SqlDbType.VarChar).Value = contact1.strC2;
                    comm.Parameters.Add("@tpd_ciudad", SqlDbType.VarChar).Value = contact1.strC3;
                    comm.Parameters.Add("@tpd_state", SqlDbType.VarChar).Value = contact1.strC4;
                    comm.Parameters.Add("@tpd_zip", SqlDbType.VarChar).Value = contact1.strC5;
                    comm.Parameters.Add("@tpd_country", SqlDbType.VarChar).Value = contact1.strC6;
                    comm.Parameters.Add("@tpd_telefono1", SqlDbType.VarChar).Value = contact1.strC7;
                    comm.Parameters.Add("@tpd_telefono2", SqlDbType.VarChar).Value = contact1.strC8;
                    comm.Parameters.Add("@tpd_telefono3", SqlDbType.VarChar).Value = contact1.strC11;
                    comm.Parameters.Add("@tpd_work_email", SqlDbType.VarChar).Value = contact1.strC9;
                    comm.Parameters.Add("@tpd_personal_email", SqlDbType.VarChar).Value = contact1.strC10;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }
            }

           
            result = new RE_GenericBean();
            result.intC1 = per_id;
            result.intC2 = per_id;
            result.intC3 = emp_id;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log("Fallo el ingreso de persona:  "+personalinf.strC2 + e.Message.ToString() + e.StackTrace.ToString());
            return null;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    
    public static bool InsertaContactInfo(RE_GenericBean contactinfo, bool update)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (!update)
            {
                comm.CommandText = "insert into tbl_persona_direccion (tpd_id, tpd_tper_id, tpd_direccion1, tpd_direccion2, tpd_ciudad, tpd_state, tpd_zip, tpd_country, tpd_telefono1, tpd_telefono2, tpd_telefono3, tpd_work_email, tpd_personal_email)";
                comm.CommandText += "values ((select COALESCE(max(tpd_id)+1,1) from tbl_persona_direccion),  @tpd_tper_id, @tpd_direccion1, @tpd_direccion2, @tpd_ciudad, @tpd_state, @tpd_zip, @tpd_country, @tpd_telefono1, @tpd_telefono2, @tpd_telefono3, @tpd_work_email, @tpd_personal_email)";
                comm.Parameters.Add("@tpd_tper_id", SqlDbType.Int).Value = contactinfo.intC2;
                comm.Parameters.Add("@tpd_direccion1", SqlDbType.VarChar).Value = contactinfo.strC1;
                comm.Parameters.Add("@tpd_direccion2", SqlDbType.VarChar).Value = contactinfo.strC2;
                comm.Parameters.Add("@tpd_ciudad", SqlDbType.VarChar).Value = contactinfo.strC3;
                comm.Parameters.Add("@tpd_state", SqlDbType.VarChar).Value = contactinfo.strC4;
                comm.Parameters.Add("@tpd_zip", SqlDbType.VarChar).Value = contactinfo.strC5;
                comm.Parameters.Add("@tpd_country", SqlDbType.VarChar).Value = contactinfo.strC6;
                comm.Parameters.Add("@tpd_telefono1", SqlDbType.VarChar).Value = contactinfo.strC7;
                comm.Parameters.Add("@tpd_telefono2", SqlDbType.VarChar).Value = contactinfo.strC8;
                comm.Parameters.Add("@tpd_telefono3", SqlDbType.VarChar).Value = contactinfo.strC11;
                comm.Parameters.Add("@tpd_work_email", SqlDbType.VarChar).Value = contactinfo.strC9;
                comm.Parameters.Add("@tpd_personal_email", SqlDbType.VarChar).Value = contactinfo.strC10;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            else
            {
                comm.CommandText = "update tbl_persona_direccion set tpd_tper_id=@tpd_tper_id, tpd_direccion1=@tpd_direccion1, tpd_direccion2=@tpd_direccion2, tpd_ciudad=@tpd_ciudad, tpd_state=@tpd_state, tpd_zip=@tpd_zip, tpd_country=@tpd_country, tpd_telefono1=@tpd_telefono1, tpd_telefono2=@tpd_telefono2, tpd_telefono3=@tpd_telefono3, tpd_work_email=@tpd_work_email, tpd_personal_email=@tpd_personal_email";
                comm.CommandText += " where tpd_id=@tpd_id";
                comm.Parameters.Add("@tpd_id", SqlDbType.Int).Value = contactinfo.intC1;
                comm.Parameters.Add("@tpd_tper_id", SqlDbType.Int).Value = contactinfo.intC2;
                comm.Parameters.Add("@tpd_direccion1", SqlDbType.VarChar).Value = contactinfo.strC1;
                comm.Parameters.Add("@tpd_direccion2", SqlDbType.VarChar).Value = contactinfo.strC2;
                comm.Parameters.Add("@tpd_ciudad", SqlDbType.VarChar).Value = contactinfo.strC3;
                comm.Parameters.Add("@tpd_state", SqlDbType.VarChar).Value = contactinfo.strC4;
                comm.Parameters.Add("@tpd_zip", SqlDbType.VarChar).Value = contactinfo.strC5;
                comm.Parameters.Add("@tpd_country", SqlDbType.VarChar).Value = contactinfo.strC6;
                comm.Parameters.Add("@tpd_telefono1", SqlDbType.VarChar).Value = contactinfo.strC7;
                comm.Parameters.Add("@tpd_telefono2", SqlDbType.VarChar).Value = contactinfo.strC8;
                comm.Parameters.Add("@tpd_telefono3", SqlDbType.VarChar).Value = contactinfo.strC11;
                comm.Parameters.Add("@tpd_work_email", SqlDbType.VarChar).Value = contactinfo.strC9;
                comm.Parameters.Add("@tpd_personal_email", SqlDbType.VarChar).Value = contactinfo.strC10;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }

            result = true;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_GenericBean InsertaEmergencyInfo(RE_GenericBean emergencyinfo, bool update)
    {
        RE_GenericBean result ;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            int tpem_id = 0;
            result = new RE_GenericBean();
            if (!update)
            {
                comm.CommandText = "select COALESCE(max(tpem_id)+1,1) from tbl_persona_emergencia";
                tpem_id = int.Parse(comm.ExecuteScalar().ToString());
                comm.Parameters.Clear();


                comm.CommandText = "insert into tbl_persona_emergencia(tpem_id, tpem_tper_id, tpem_nombre, tpem_relacion, tpem_direccion1, tpem_direccion2, tpem_ciudad, tpem_state, tpem_zip, tpem_country, tpem_telefono1, tpem_telefono2, tpem_telefono3, tpem_email)";
                comm.CommandText += "values (@tpem_id,  @tpem_tper_id, @tpem_nombre, @tpem_relacion, @tpem_direccion1, @tpem_direccion2, @tpem_ciudad, @tpem_state, @tpem_zip, @tpem_country, @tpem_telefono1, @tpem_telefono2, @tpem_telefono3, @tpem_email)";
                comm.Parameters.Add("@tpem_id", SqlDbType.Int).Value = tpem_id;
                comm.Parameters.Add("@tpem_tper_id", SqlDbType.Int).Value = emergencyinfo.intC2;
                comm.Parameters.Add("@tpem_nombre", SqlDbType.VarChar).Value = emergencyinfo.strC1;
                comm.Parameters.Add("@tpem_relacion", SqlDbType.VarChar).Value = emergencyinfo.strC2;
                comm.Parameters.Add("@tpem_direccion1", SqlDbType.VarChar).Value = emergencyinfo.strC3;
                comm.Parameters.Add("@tpem_direccion2", SqlDbType.VarChar).Value = emergencyinfo.strC4;
                comm.Parameters.Add("@tpem_ciudad", SqlDbType.VarChar).Value = emergencyinfo.strC5;
                comm.Parameters.Add("@tpem_state", SqlDbType.VarChar).Value = emergencyinfo.strC6;
                comm.Parameters.Add("@tpem_zip", SqlDbType.VarChar).Value = emergencyinfo.strC7;
                comm.Parameters.Add("@tpem_country", SqlDbType.VarChar).Value = emergencyinfo.strC8;
                comm.Parameters.Add("@tpem_telefono1", SqlDbType.VarChar).Value = emergencyinfo.strC9;
                comm.Parameters.Add("@tpem_telefono2", SqlDbType.VarChar).Value = emergencyinfo.strC11;
                comm.Parameters.Add("@tpem_telefono3", SqlDbType.VarChar).Value = emergencyinfo.strC12;
                comm.Parameters.Add("@tpem_email", SqlDbType.VarChar).Value = emergencyinfo.strC10;
                
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                result.boolC1 = true;
                result.intC1 = tpem_id;
            }
            else
            {
                comm.CommandText = "update tbl_persona_emergencia set tpem_tper_id=@tpem_tper_id, tpem_nombre=@tpem_nombre, tpem_relacion=@tpem_relacion, tpem_direccion1=@tpem_direccion1, tpem_direccion2=@tpem_direccion2, tpem_ciudad=@tpem_ciudad, tpem_state=@tpem_state, tpem_zip=@tpem_zip, tpem_country=@tpem_country, tpem_telefono1=@tpem_telefono1, tpem_telefono2=@tpem_telefono2, tpem_telefono3=@tpem_telefono3, tpem_email=@tpem_email";
                comm.CommandText += " where tpem_id=@tpem_id";
                comm.Parameters.Add("@tpem_id", SqlDbType.Int).Value = emergencyinfo.intC1;
                comm.Parameters.Add("@tpem_tper_id", SqlDbType.Int).Value = emergencyinfo.intC2;
                comm.Parameters.Add("@tpem_nombre", SqlDbType.VarChar).Value = emergencyinfo.strC1;
                comm.Parameters.Add("@tpem_relacion", SqlDbType.VarChar).Value = emergencyinfo.strC2;
                comm.Parameters.Add("@tpem_direccion1", SqlDbType.VarChar).Value = emergencyinfo.strC3;
                comm.Parameters.Add("@tpem_direccion2", SqlDbType.VarChar).Value = emergencyinfo.strC4;
                comm.Parameters.Add("@tpem_ciudad", SqlDbType.VarChar).Value = emergencyinfo.strC5;
                comm.Parameters.Add("@tpem_state", SqlDbType.VarChar).Value = emergencyinfo.strC6;
                comm.Parameters.Add("@tpem_zip", SqlDbType.VarChar).Value = emergencyinfo.strC7;
                comm.Parameters.Add("@tpem_country", SqlDbType.VarChar).Value = emergencyinfo.strC8;
                comm.Parameters.Add("@tpem_telefono1", SqlDbType.VarChar).Value = emergencyinfo.strC9;
                comm.Parameters.Add("@tpem_telefono2", SqlDbType.VarChar).Value = emergencyinfo.strC11;
                comm.Parameters.Add("@tpem_telefono3", SqlDbType.VarChar).Value = emergencyinfo.strC12;
                comm.Parameters.Add("@tpem_email", SqlDbType.VarChar).Value = emergencyinfo.strC10;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();


                result.boolC1 = true;
                result.intC1 = emergencyinfo.intC1;
               
            }
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            result = new RE_GenericBean();
            result.boolC1 = false;
            
            return result;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static bool InsertaCuentaBanco(RE_GenericBean cban, bool update)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (!update)
            {


                comm.CommandText = "insert into tbl_empleado_accbank (emp_accbank_acc#, emp_accbank_bank, emp_accbank_type, emp_accbank_empid, emp_accbank_status) ";
                comm.CommandText += "values (@empacc, @empbank, @acctype, @accempid, 1)";
                comm.Parameters.Add("@empacc", SqlDbType.VarChar).Value = cban.strC1;//acc#
                comm.Parameters.Add("@empbank", SqlDbType.Int).Value = cban.intC2;//bank
                comm.Parameters.Add("@acctype", SqlDbType.VarChar).Value = cban.strC2;//type
                comm.Parameters.Add("@accempid", SqlDbType.Int).Value = cban.intC1;//empid
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            else
            {
                comm.CommandText = "update tbl_empleado_accbank set emp_accbank_acc#=@empacc, emp_accbank_bank=@empbank, emp_accbank_type=@acctype ";
                comm.CommandText += " where emp_accbank_id=@bankaccid ";
                comm.Parameters.Add("@empacc", SqlDbType.Int).Value = cban.strC1;
                comm.Parameters.Add("@empbank", SqlDbType.Int).Value = cban.intC2;
                comm.Parameters.Add("@acctype", SqlDbType.Int).Value = cban.strC2;
                comm.Parameters.Add("@bankaccid", SqlDbType.Int).Value = cban.intC3;
               
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }

            result = true;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static bool InsertaExpediente(RE_GenericBean exp, bool update)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (!update)
            {
                comm.CommandText = "update tbl_expediente set tex_fecha_fin=@tex_fecha_fin where tex_fecha_fin is null and tex_emp_id=@tex_emp_id";
                comm.Parameters.Add("@tex_emp_id", SqlDbType.Int).Value = exp.intC2;
                comm.Parameters.Add("@tex_fecha_fin", SqlDbType.Date).Value = exp.strC2;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();


                comm.CommandText = "insert into tbl_expediente (tex_id, tex_emp_id, tex_tpe_id, tex_ttc_id, tex_status, tex_fecha_inicio, tex_salario, tex_bonificacion, tex_commission, tex_other_commission, tex_currency) ";
                comm.CommandText += "values ((select COALESCE(max(tex_id)+1,1) from tbl_expediente), @tex_emp_id, @tex_tpe_id, @tex_ttc_id, @tex_status, @tex_fecha_inicio, @tex_salario, @tex_bonificacion, @tex_commission, @tex_other_commission, @tex_currency)";
                comm.Parameters.Add("@tex_emp_id", SqlDbType.Int).Value = exp.intC2;
                comm.Parameters.Add("@tex_tpe_id", SqlDbType.Int).Value = exp.intC3;
                comm.Parameters.Add("@tex_ttc_id", SqlDbType.Int).Value = exp.intC4;
                comm.Parameters.Add("@tex_status", SqlDbType.Int).Value = exp.intC5;
                comm.Parameters.Add("@tex_fecha_inicio", SqlDbType.Date).Value = exp.strC2;
                comm.Parameters.Add("@tex_salario", SqlDbType.Decimal).Value = exp.decC1;
                comm.Parameters.Add("@tex_bonificacion", SqlDbType.Decimal).Value = exp.decC2;
                comm.Parameters.Add("@tex_commission", SqlDbType.Decimal).Value = exp.decC3;
                comm.Parameters.Add("@tex_other_commission", SqlDbType.Decimal).Value = exp.decC4;
                comm.Parameters.Add("@tex_currency", SqlDbType.VarChar).Value = exp.strC1;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            else
            {
                comm.CommandText = "update tbl_expediente set tex_emp_id=@tex_emp_id, tex_tpe_id=@tex_tpe_id, tex_ttc_id=@tex_ttc_id, tex_status=@tex_status, tex_fecha_inicio=@tex_fecha_inicio, ";
                comm.CommandText += "tex_salario=@tex_salario, tex_bonificacion=@tex_bonificacion, tex_commission=@tex_commission, tex_other_commission=@tex_other_commission, tex_currency=@tex_currency ";
                comm.CommandText += " where tex_id=@tex_id";
                comm.Parameters.Add("@tex_id", SqlDbType.Int).Value = exp.intC1;
                comm.Parameters.Add("@tex_emp_id", SqlDbType.Int).Value = exp.intC2;
                comm.Parameters.Add("@tex_tpe_id", SqlDbType.Int).Value = exp.intC3;
                comm.Parameters.Add("@tex_ttc_id", SqlDbType.Int).Value = exp.intC4;
                comm.Parameters.Add("@tex_status", SqlDbType.Int).Value = exp.intC5;
                comm.Parameters.Add("@tex_fecha_inicio", SqlDbType.Date).Value = exp.strC2;
                comm.Parameters.Add("@tex_salario", SqlDbType.Decimal).Value = exp.decC1;
                comm.Parameters.Add("@tex_bonificacion", SqlDbType.Decimal).Value = exp.decC2;
                comm.Parameters.Add("@tex_commission", SqlDbType.Decimal).Value = exp.decC3;
                comm.Parameters.Add("@tex_other_commission", SqlDbType.Decimal).Value = exp.decC4;
                comm.Parameters.Add("@tex_currency", SqlDbType.VarChar).Value = exp.strC1;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }

            result = true;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static bool UpdateAffiliationInfo(RE_GenericBean affilliateinfo, bool update)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;

            comm.CommandText = "update  tbl_persona set tper_idper=@tper_idper, tper_nit=@tper_nit, tper_igss= @tper_igss, tper_irtra=@tper_irtra, tper_tipo_idper=@tper_typoid  ";
                comm.CommandText += "where tper_id=@tper_id ";
                comm.Parameters.Add("@tper_id", SqlDbType.Int).Value = affilliateinfo.strC6;
                comm.Parameters.Add("@tper_idper", SqlDbType.VarChar).Value = affilliateinfo.strC1;
                comm.Parameters.Add("@tper_nit", SqlDbType.VarChar).Value = affilliateinfo.strC2;
                comm.Parameters.Add("@tper_igss", SqlDbType.VarChar).Value = affilliateinfo.strC3;
                comm.Parameters.Add("@tper_irtra", SqlDbType.VarChar).Value = affilliateinfo.strC4;
                comm.Parameters.Add("@tper_typoid", SqlDbType.Int).Value = int.Parse(affilliateinfo.strC5);
                
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            
            result = true;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_GenericBean InsertJobPositionEmp(RE_GenericBean puesto, bool update)
    {
        RE_GenericBean result ;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;

           // int emp_id = getIDEmpleadoCuenta(puesto.intC5, puesto.intC4);
            int emp_id = puesto.intC7;

            if (emp_id != 0)
            {

                comm.CommandText = "update tbl_empleado set emp_tper_id=@emp_tper_id, emp_tcu_id=@emp_tcu_id, emp_codigo_empleado=@emp_codigo_empleado, emp_xml=@emp_xml, emp_hired_date=@emp_hired_date, emp_end_date=@emp_end_date, emp_jefe=@emp_jefe ";
                comm.CommandText += " where emp_id=@emp_id";
                comm.Parameters.Add("@emp_id", SqlDbType.Int).Value = emp_id;
                comm.Parameters.Add("@emp_tper_id", SqlDbType.Int).Value = puesto.intC5;
                comm.Parameters.Add("@emp_tcu_id", SqlDbType.Int).Value = puesto.intC4;
                comm.Parameters.Add("@emp_codigo_empleado", SqlDbType.VarChar).Value = puesto.strC6;
                comm.Parameters.Add("@emp_xml", SqlDbType.Xml).Value = "";
                if (puesto.strC1.Equals("")) puesto.strC1 = "1/1/1900";
                if (puesto.strC2.Equals("")) puesto.strC2 = "1/1/1900";
                comm.Parameters.Add("@emp_hired_date", SqlDbType.VarChar).Value = puesto.strC1;
                comm.Parameters.Add("@emp_end_date", SqlDbType.VarChar).Value = puesto.strC2;
                comm.Parameters.Add("@emp_jefe", SqlDbType.Int).Value = puesto.intC8;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = "update tbl_expediente set tex_fecha_fin=@tex_fecha_fin,tex_tpe_id=@tex_tpe_id, tex_status=@tex_status,tex_fecha_inicio=@tex_fecha_inicio ";
                comm.CommandText += "  where  tex_emp_id=@tex_emp_id ";
                comm.Parameters.Add("@tex_emp_id", SqlDbType.Int).Value = emp_id;
                if (puesto.strC3.Equals("")) puesto.strC3 = "1/1/1900";
                comm.Parameters.Add("@tex_fecha_inicio", SqlDbType.VarChar).Value = puesto.strC3;
                comm.Parameters.Add("@tex_fecha_fin", SqlDbType.VarChar).Value = puesto.strC2;
                comm.Parameters.Add("@tex_tpe_id", SqlDbType.VarChar).Value = puesto.intC1;
                comm.Parameters.Add("@tex_status", SqlDbType.VarChar).Value = puesto.intC6;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                result = new RE_GenericBean();
                result.boolC1 = true;
                result.intC7 = emp_id;
                result.intC1 = emp_id;
            }
            else {
                result = new RE_GenericBean();
                result.boolC1 = false;
                result.intC7 = 0;
                result.intC1 = 0;
            }


            
            
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            result = new RE_GenericBean();
            result.boolC1 = false;
            result.intC7 = 0;
            result.intC1 = 0;
            return result;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static bool InsertSalaryEmp(RE_GenericBean salary)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
           
            //int emp_id = getIDEmpleadoCuenta(salary.intC1, salary.intC4);
            int emp_id = salary.intC5;
            int exp_id = 0;
            comm.CommandText = "select tex_id from tbl_expediente where tex_emp_id= @tex_emp_id ";
            comm.Parameters.Add("@tex_emp_id", SqlDbType.Int).Value = emp_id;
            object obj = comm.ExecuteScalar();
            if (obj != null)
            {
             
                exp_id = int.Parse(comm.ExecuteScalar().ToString());
            }
           
            comm.Parameters.Clear();
            if (exp_id == 0)
            {

                comm.CommandText = "insert into tbl_expediente (tex_id, tex_emp_id, tex_ttc_id,  tex_salario, tex_bonificacion, tex_commission, tex_other_commission, tex_currency, tex_severance, tex_parking,tex_agreement) ";
                comm.CommandText += "values ((select COALESCE(max(tex_id)+1,1) from tbl_expediente), @tex_emp_id,  @tex_ttc_id,  @tex_salario, @tex_bonificacion, @tex_commission, @tex_other_commission, @tex_currency, @tex_severance, @tex_parking, @tex_agreement)";
                comm.Parameters.Add("@tex_emp_id", SqlDbType.Int).Value = emp_id;
                comm.Parameters.Add("@tex_ttc_id", SqlDbType.Int).Value = salary.intC2;
                comm.Parameters.Add("@tex_salario", SqlDbType.Decimal).Value = salary.decC1;
                comm.Parameters.Add("@tex_bonificacion", SqlDbType.Decimal).Value = salary.decC2;
                comm.Parameters.Add("@tex_commission", SqlDbType.Decimal).Value = salary.decC3;
                comm.Parameters.Add("@tex_other_commission", SqlDbType.Decimal).Value = salary.decC4;
                comm.Parameters.Add("@tex_currency", SqlDbType.VarChar).Value = salary.strC1;
                comm.Parameters.Add("@tex_severance", SqlDbType.Decimal).Value = salary.decC5;
                comm.Parameters.Add("@tex_parking",SqlDbType.Int).Value = salary.intC6;
                comm.Parameters.Add("@tex_agreement", SqlDbType.Decimal).Value = salary.decC6;
              
                

                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

            }
            else
            {


                comm.CommandText = "update tbl_expediente set tex_ttc_id=@tex_ttc_id,  tex_salario=@tex_salario, tex_bonificacion=@tex_bonificacion, tex_commission=@tex_commission, tex_other_commission=@tex_other_commission, tex_currency=@tex_currency, tex_severance=@tex_severance, tex_parking=@tex_parking, tex_agreement=@tex_agreement  where tex_emp_id=@tex_emp_id ";
                comm.Parameters.Add("@tex_emp_id", SqlDbType.Int).Value = emp_id;
                comm.Parameters.Add("@tex_ttc_id", SqlDbType.Int).Value = salary.intC2;
                comm.Parameters.Add("@tex_salario", SqlDbType.Decimal).Value = salary.decC1;
                comm.Parameters.Add("@tex_bonificacion", SqlDbType.Decimal).Value = salary.decC2;
                comm.Parameters.Add("@tex_commission", SqlDbType.Decimal).Value = salary.decC3;
                comm.Parameters.Add("@tex_other_commission", SqlDbType.Decimal).Value = salary.decC4;
                comm.Parameters.Add("@tex_currency", SqlDbType.VarChar).Value = salary.strC1;
                comm.Parameters.Add("@tex_severance", SqlDbType.Decimal).Value = salary.decC5;
                comm.Parameters.Add("@tex_parking", SqlDbType.Int).Value = salary.intC6;
                comm.Parameters.Add("@tex_agreement", SqlDbType.Decimal).Value = salary.decC6;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }



            result = true;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static bool InsertFoto(string sphoto, int per_id)
    {
        bool result = false;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
       
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
           
           
                comm.CommandText = "update  tbl_persona set tper_foto=@foto where tper_id=@tper_id";
                
                comm.Parameters.Add("@tper_id", SqlDbType.Int).Value = per_id;
                comm.Parameters.Add("@foto", SqlDbType.VarChar).Value = sphoto;
                
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            
            result = true;
           
        }
        catch (Exception e)
        {
            
            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static bool InsertBank(RE_GenericBean bank, bool update)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (!update)
            {
                comm.CommandText = "insert into tbl_banco ( nombre, descripcion, country) "; //inserta a la tabla banco los valores a cada campo
                comm.CommandText += "values (@nombre, @descripcion, @country)";// ingresa los valores
                comm.Parameters.Add("@id", SqlDbType.Int).Value = bank.intC1;//id
                comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = bank.strC1;//Nombre
                comm.Parameters.Add("@country", SqlDbType.Int).Value = bank.intC2;//Pais
                comm.Parameters.Add("@descripcion", SqlDbType.VarChar).Value = bank.strC3;//descripcion
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            else
            {
                comm.CommandText = "update tbl_banco set nombre=@name, descripcion=@descripcion, country=@country ";//Actualiza la tabla con los valores
                comm.CommandText += "where id=@id";// donde el id del fdormulario 
                comm.Parameters.Add("@id", SqlDbType.Int).Value = bank.intC1;//id
                comm.Parameters.Add("@name", SqlDbType.VarChar).Value = bank.strC1;//Nombre
                comm.Parameters.Add("@country", SqlDbType.Int).Value = bank.intC2;//Pais
                comm.Parameters.Add("@descripcion", SqlDbType.VarChar).Value = bank.strC3;//descripcion
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            result = true;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }


    public static RE_GenericBean InsertaDocumento(RE_GenericBean re, bool actualiza)
    {
        RE_GenericBean result = new RE_GenericBean();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();

        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            if (actualiza) {
                comm.CommandText = "update  tbl_empleado_documento set documento_id=@documento_id, path=@path where id=@iddoc";

                comm.Parameters.Add("@iddoc", SqlDbType.Int).Value = re.intC5;
                comm.Parameters.Add("@documento_id", SqlDbType.VarChar).Value = re.intC2;
                comm.Parameters.Add("@path", SqlDbType.VarChar).Value = re.strC1;

                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                result.intC1= 1;
                result.intC2= re.intC5;
            
            }

            else
            {
                
                comm.CommandText = "select COALESCE(max(id)+1,1) from tbl_empleado_documento";
                result.intC2= int.Parse(comm.ExecuteScalar().ToString());
                comm.Parameters.Clear();

                comm.CommandText = "insert into   tbl_empleado_documento (empleado_id, documento_id, path) values (@emp_id, @documento_id, @path)";

                comm.Parameters.Add("@emp_id", SqlDbType.Int).Value = re.intC1;
                comm.Parameters.Add("@documento_id", SqlDbType.VarChar).Value = re.intC2;
                comm.Parameters.Add("@path", SqlDbType.VarChar).Value = re.strC1;

                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                result.intC1=1;
                result.intC2 = result.intC2;

            }
            

        }
        catch (Exception e)
        {

            utility.log(e.Message.ToString());
            return result;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_GenericBean InsertaDocumentoCom(RE_GenericBean re, bool actualiza)
    {
        RE_GenericBean result = new RE_GenericBean();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();

        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            if (actualiza)
            {
                comm.CommandText = " update  tbl_compania_documento set documento_id=@documento_id, path=@path where id=@iddoc ";

                comm.Parameters.Add("@iddoc", SqlDbType.Int).Value = re.intC5;
                comm.Parameters.Add("@documento_id", SqlDbType.VarChar).Value = re.intC2;
                comm.Parameters.Add("@path", SqlDbType.VarChar).Value = re.strC1;

                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                result.intC1 = 1;
                result.intC2 = re.intC5;

            }

            else
            {

                comm.CommandText = "select COALESCE(max(id)+1,1) from tbl_compania_documento";
                result.intC2 = int.Parse(comm.ExecuteScalar().ToString());
                comm.Parameters.Clear();

                comm.CommandText = "insert into   tbl_compania_documento (compania_id, documento_id, path) values (@com_id, @documento_id, @path)";

                comm.Parameters.Add("@com_id", SqlDbType.Int).Value = re.intC1;
                comm.Parameters.Add("@documento_id", SqlDbType.VarChar).Value = re.intC2;
                comm.Parameters.Add("@path", SqlDbType.VarChar).Value = re.strC1;

                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                result.intC1 = 1;
                result.intC2 = result.intC2;

            }


        }
        catch (Exception e)
        {

            utility.log(e.Message.ToString());
            return result;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }


    public static bool InsertDoc(RE_GenericBean Docs, bool update)
    {

        bool result = false;

        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();

        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            if (!update)
            {
                comm.CommandText = "insert into tbl_tipo_documento(nombre, descripcion, obligatorio, orden, tipo)"; //inserta en la tabla documento los campos
                comm.CommandText += "values (@nombre,@descripcion, @obligatorio, (select COALESCE(max(orden)+1,1) from tbl_tipo_documento),@tipo)"; // los valores y ademas el auto incremento del orden
                //comm.Parameters.Add("@id", SqlDbType.Int).Value = Docum.intC1;
                comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = Docs.strC1;//nombre
                comm.Parameters.Add("@descripcion", SqlDbType.VarChar).Value = Docs.strC3;//descripcion
                comm.Parameters.Add("@obligatorio", SqlDbType.Int).Value = Docs.intC2;//obligatorio
                // comm.Parameters.Add("@orden", SqlDbType.Int).Value = Docum.intC3;//orden
                comm.Parameters.Add("@tipo", SqlDbType.Int).Value = Docs.intC3;// tipo documento
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            else
            {// , orden=@orden   id=@id, 
                comm.CommandText = "update tbl_tipo_documento set nombre=@nombre, descripcion=@descripcion, obligatorio=@obligatorio, tipo=@tipo "; // actualiza los campos con sus parametros determinados //descripcion=@descripcion
                comm.CommandText += "where id=@id";
                comm.Parameters.Add("@id", SqlDbType.Int).Value = Docs.intC1;//id
                comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = Docs.strC1;//nombre
                comm.Parameters.Add("@descripcion", SqlDbType.VarChar).Value = Docs.strC3;//descripcion
                comm.Parameters.Add("@obligatorio", SqlDbType.Int).Value = Docs.intC2;// obligatorio
                //comm.Parameters.Add("@orden", SqlDbType.Int).Value = Docum.intC3;
                comm.Parameters.Add("@tipo", SqlDbType.Int).Value = Docs.intC3;// tipo documento
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            result = true;

        }
        catch (Exception e)
        {

            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }


    public static bool insertDocsComp(ArrayList docs, int id_comp)
    {
        // Agrega o edita los permisos de los documentos de compañia
        bool result = false;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            foreach (RE_GenericBean rg in docs)
            {
                comm.CommandText = " MERGE tbl_vista_documento ";
                comm.CommandText += " USING (SELECT @tco_id AS ID1, @tdc_id AS ID2) AS EX ON (EX.ID1 = tbl_vista_documento.tco_id AND EX.ID2 = tbl_vista_documento.tdc_id) ";
                comm.CommandText += " WHEN MATCHED THEN ";
                comm.CommandText += " UPDATE SET obligatorio=@obl, visible=@vis ";
                comm.CommandText += " WHEN NOT MATCHED THEN ";
                comm.CommandText += " INSERT (tco_id, tdc_id, obligatorio, visible) VALUES (@tco_id, @tdc_id, @obl, @vis); ";
                comm.Parameters.Add("@tco_id", SqlDbType.Int).Value = id_comp;
                comm.Parameters.Add("@tdc_id", SqlDbType.Int).Value = rg.intC1;
                comm.Parameters.Add("@obl", SqlDbType.VarChar).Value = rg.intC2;
                comm.Parameters.Add("@vis", SqlDbType.VarChar).Value = rg.intC3;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            result = true;

        }
        catch (Exception e)
        {

            utility.log(e.Message.ToString());
            return false;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }


    public static int insertPayroll(RE_GenericBean rg, bool update){
    
    int result= 0;
    SqlTransaction transaction;
    SqlConnection conn = null;
    SqlCommand comm = null;
    SqlDataReader reader = null;
    conn = OpenConnection();
    transaction = conn.BeginTransaction();
    try
    {
        comm = new SqlCommand();
        comm.Connection = conn;
        comm.Transaction = transaction;
        comm.CommandType = CommandType.Text;

       
            string temp="";
            if (HttpContext.Current.Session["user"] != null)
            {
                RE_GenericBean user = (RE_GenericBean)HttpContext.Current.Session["user"];
                temp=  rg.strC3 + " " + rg.strC4;
            }
            int id = 0;
            comm.CommandText = "select COALESCE(max(id)+1,1) from tbl_payroll"; //va a traer el id que tocaria
            
            object obj = comm.ExecuteScalar();
            if (obj != null)
            {

                id = int.Parse(comm.ExecuteScalar().ToString());
            }

            comm.CommandText = "insert into tbl_payroll(id, name, startdate, enddate, creator,completed, closed, comments, costextraorder )"; //inserta en la tabla payroll los campos
            comm.CommandText += "values (@id,@nombre,@startdate, @enddate, @creator,0,0,@comments,@costextraorder)"; // los valores y ademas el auto incremento del orden
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;//id
            comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = rg.strC1;//nombre
            comm.Parameters.Add("@startdate", SqlDbType.VarChar).Value = rg.strC2;//start date
            comm.Parameters.Add("@enddate", SqlDbType.VarChar).Value = rg.strC3;//end date
            comm.Parameters.Add("@creator", SqlDbType.VarChar).Value = temp;//creator
            comm.Parameters.Add("@comments", SqlDbType.VarChar).Value = rg.strC4;// comments
            comm.Parameters.Add("@costextraorder", SqlDbType.Decimal).Value = rg.decC1;
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();


            /*ahora genero la tabla de payrool con los empleados vigentes al momento de la creacion*/
            comm.CommandText = "SELECT     tbl_empleado.emp_id, tbl_persona.tper_nombre +' '+ tbl_persona.tper_apellido, tbl_expediente.tex_ttc_id, tbl_expediente.tex_currency,  ";
            comm.CommandText += " tbl_expediente.tex_salario, tbl_expediente.tex_bonificacion, tbl_expediente.tex_severance, tbl_expediente.tex_other_commission, tbl_expediente.tex_commission, tbl_expediente.tex_fecha_inicio, tbl_expediente.tex_fecha_fin,tbl_empleado.emp_tcu_id, tbl_cuenta.tcu_tco_id, tbl_expediente.tex_agreement  ";
            comm.CommandText += "  FROM   tbl_empleado INNER JOIN tbl_expediente ON tbl_empleado.emp_id = tbl_expediente.tex_emp_id INNER JOIN tbl_persona ON tbl_empleado.emp_tper_id = tbl_persona.tper_id INNER JOIN tbl_cuenta ON tbl_empleado.emp_tcu_id = tbl_cuenta.tcu_id  ";


            comm.CommandText += "  WHERE     (tbl_empleado.emp_hired_date IS NOT NULL) AND (tbl_empleado.emp_hired_date <= @planfin) ";
            comm.CommandText += " AND  (tbl_empleado.emp_end_date = '01/01/1900' OR  tbl_empleado.emp_end_date >= @planinicio )" ;
            comm.CommandText += " ORDER BY  tbl_persona.tper_nombre ";

            comm.Parameters.Add("@planinicio", SqlDbType.VarChar).Value = rg.strC2; //planinicio;
            comm.Parameters.Add("@planfin", SqlDbType.VarChar).Value= rg.strC3; //planfin;
            reader = comm.ExecuteReader();              
        
            int cont = 0;
            ArrayList ar = new ArrayList();
            while (reader.Read())
            {
                RE_GenericBean r = new RE_GenericBean();
                cont++;
                if (!reader.IsDBNull(0)) r.intC1 = reader.GetInt32(0); //employee
                if (!reader.IsDBNull(1)) r.strC1 = reader.GetString(1); //last name

                if (!reader.IsDBNull(2)) r.intC2 = reader.GetInt32(2); //ttc

                if (!reader.IsDBNull(3)) r.strC3 = reader.GetString(3); //currency
                if (!reader.IsDBNull(4)) r.decC1 = reader.GetDecimal(4); //salario
                if (!reader.IsDBNull(5)) r.decC2 = reader.GetDecimal(5); //bonificacion
                if (!reader.IsDBNull(6)) r.decC3 = reader.GetDecimal(6); //severeance
                if (!reader.IsDBNull(7)) r.decC4 = reader.GetDecimal(7); //severance bonus
                if (!reader.IsDBNull(8)) r.decC5 = reader.GetDecimal(8); //igss value
                if (!reader.IsDBNull(9)) r.strC4 = reader.GetDateTime(9).ToShortDateString(); //fecha inicio
                if (!reader.IsDBNull(10)) r.strC5 = reader.GetDateTime(10).ToShortDateString(); //fecha fin
                if (!reader.IsDBNull(13)) r.decC12 = reader.GetDecimal(13); //sev agreement
                //parametros employeeid, name,  salario,   bonifica,  severance, sevbonus, igss$, sevagree, flagigss, flasgssev
                
                ar.Add(r);
            }
            reader.Close();
            comm.Parameters.Clear();
            foreach(RE_GenericBean rr in ar){
                comm.CommandText = "insert into tbl_payroll_detail (payroll_id, emp_id,igss, severance, cost_orderext, salary, bonus, sevvalue, sevbonus, sevagreement, igssvalue) ";
                comm.CommandText+= " values (@payroll_id, @emp_id,1, 1, @cost_orderext, @salary,@bonus, @sevvalue, @sevbonus, @sevagreement,@igssvalue)";
                comm.Parameters.Add("@payroll_id", SqlDbType.Int).Value = id;
                comm.Parameters.Add("@emp_id",SqlDbType.Int).Value = rr.intC1;
                comm.Parameters.Add("@cost_orderext",SqlDbType.Decimal).Value = rg.decC1;
                comm.Parameters.Add("@salary", SqlDbType.Decimal).Value = rr.decC1;
                comm.Parameters.Add("@bonus", SqlDbType.Decimal).Value = rr.decC2;
                comm.Parameters.Add("@sevvalue", SqlDbType.Decimal).Value = rr.decC3;
                comm.Parameters.Add("@sevbonus", SqlDbType.Decimal).Value = rr.decC4;
                comm.Parameters.Add("@sevagreement", SqlDbType.Decimal).Value = rr.decC12;
                comm.Parameters.Add("@igssvalue", SqlDbType.Decimal).Value = rr.decC5;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

            }
            result = id;
            transaction.Commit();
        
        

    }
    catch (Exception e)
    {
        transaction.Rollback();
        utility.log(e.Message.ToString());
        return 0;
    }
    finally
    {
        if (reader != null) reader.Dispose();
        if (comm != null) comm.Dispose();
        if (conn != null)
        {
            conn.Close();
            conn.Dispose();
        }
    }
    return result;
    
    
    }
    public static int closePayroll(int closevalue, int id)
    {

        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();

        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

          
                comm.CommandText = "update  tbl_payroll set completed=@completed where id=@id"; //inserta en la tabla documento los campos
                comm.Parameters.Add("@completed",SqlDbType.Int).Value= closevalue;
                comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
                reader = comm.ExecuteReader();
                comm.Parameters.Clear();
                result = 1;
                      
        }
        catch (Exception e)
        {

            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;


    }
    public static DataSet getPayrollDetail(int payid, string where ) {
        DataSet ds = new DataSet();
        ds.Tables.Add("Tabla");
        ds.Tables[0].Columns.Add("number");//0
        ds.Tables[0].Columns.Add("Employee");//1
        ds.Tables[0].Columns.Add("Name");//2
        ds.Tables[0].Columns.Add("currency");//3
        ds.Tables[0].Columns.Add("Salary");//4
        ds.Tables[0].Columns.Add("Bonus");//5
        ds.Tables[0].Columns.Add("igsst");//6
        ds.Tables[0].Columns.Add("igssp");//7
        ds.Tables[0].Columns.Add("igsstt");//8
        ds.Tables[0].Columns.Add("IGSS");//9
        ds.Tables[0].Columns.Add("fecha incicio");//10
        ds.Tables[0].Columns.Add("fecha fin");//11
        ds.Tables[0].Columns.Add("Days works");//12
        ds.Tables[0].Columns.Add("Extra Orders");//13
        ds.Tables[0].Columns.Add("Extra Hours");//14
        ds.Tables[0].Columns.Add("Adjustmentpos");//15
        ds.Tables[0].Columns.Add("Adjustmentneg");//16
        ds.Tables[0].Columns.Add("parking");//17
        ds.Tables[0].Columns.Add("IGSSflag");//18
        ds.Tables[0].Columns.Add("severanceflag");//19
        ds.Tables[0].Columns.Add("dailyrate");//20
        ds.Tables[0].Columns.Add("t1");//21
        ds.Tables[0].Columns.Add("basesalary");//22
        ds.Tables[0].Columns.Add("ordinarysalary");//23
        ds.Tables[0].Columns.Add("costhour");//24
        ds.Tables[0].Columns.Add("costorder");//25
        ds.Tables[0].Columns.Add("payhours");//26
        ds.Tables[0].Columns.Add("payorders");//27
        ds.Tables[0].Columns.Add("t2");//28
        ds.Tables[0].Columns.Add("t3");//29
        ds.Tables[0].Columns.Add("checkamount");//30
        ds.Tables[0].Columns.Add("severance");//31
        ds.Tables[0].Columns.Add("severancebonus");//32
        ds.Tables[0].Columns.Add("severancetotal");//33
        ds.Tables[0].Columns.Add("bono13");//34
        ds.Tables[0].Columns.Add("bono14");//35
        ds.Tables[0].Columns.Add("severancemon");//36
        ds.Tables[0].Columns.Add("totalbonus");//37
        ds.Tables[0].Columns.Add("grantotal");//38
        ds.Tables[0].Columns.Add("coefhoras");//39
        ds.Tables[0].Columns.Add("agreement");//40
        ds.Tables[0].Columns.Add("extrahours2");//41
        ds.Tables[0].Columns.Add("comments");//42
        ds.Tables[0].Columns.Add("bank");//43
       

         SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        SqlConnection conn2 = null;
        SqlCommand comm2 = null;
        SqlDataReader reader2 = null;
        try
        {
            conn = OpenConnection();
            conn2 = OpenConnection();
            comm = new SqlCommand();
            comm2 = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm2.Connection = conn2;
            comm2.CommandType = CommandType.Text;

            Decimal sumaigssp = 0;
            Decimal sumaigsst=0; 
            Decimal sumaigsstt=0;
            Decimal sumachek=0;
            Decimal sumatotalbonus = 0;
            Decimal sumagrantotal = 0;
            Decimal sumabono13 = 0;
            Decimal sumabono15 = 0;

            comm.CommandText = " select convert(varchar,startdate), convert(varchar,enddate), completed from tbl_payroll where id =@id ";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = payid;
            reader = comm.ExecuteReader();
            string planinicio = "1/1/2900";
            string planfin = "1/1/1900";
            while (reader.Read()) {
                if (!reader.IsDBNull(0)) planinicio = reader.GetString(0); //fecha incuicio planilla
                if (!reader.IsDBNull(1)) planfin = reader.GetString(1); //fecha fin planilla
            
            }
            reader.Close();
            comm.Parameters.Clear();


            comm.CommandText = "SELECT     tbl_payroll_detail.emp_id, tbl_persona.tper_nombre+' '+tbl_persona.tper_apellido, tbl_payroll_detail.salary, tbl_payroll_detail.bonus, tbl_payroll_detail.sevvalue, tbl_payroll_detail.sevbonus, tbl_payroll_detail.igssvalue, tbl_empleado.emp_tcu_id, tbl_cuenta.tcu_tco_id, ";
            comm.CommandText += " tbl_payroll_detail.sevagreement, tbl_payroll_detail.dayswork, tbl_payroll_detail.extraorders, tbl_payroll_detail.extrahours, tbl_payroll_detail.adjustmentpos, tbl_payroll_detail.adjustmentneg, tbl_payroll_detail.igss, tbl_payroll_detail.parking, tbl_payroll_detail.severance, ";
            comm.CommandText += " tbl_payroll_detail.coef_hourext, tbl_payroll_detail.cost_orderext,tbl_payroll_detail.extrahours2, tbl_payroll_detail.comment, tbl_payroll_detail.bank FROM         tbl_payroll_detail INNER JOIN ";
            comm.CommandText += " tbl_empleado ON tbl_payroll_detail.emp_id = tbl_empleado.emp_id INNER JOIN tbl_persona ON tbl_empleado.emp_tper_id = tbl_persona.tper_id INNER JOIN ";
            comm.CommandText += " tbl_cuenta ON tbl_empleado.emp_tcu_id = tbl_cuenta.tcu_id WHERE tbl_payroll_detail.payroll_id=@id ORDER BY tbl_persona.tper_nombre  ";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = payid;


         
           
            reader = comm.ExecuteReader();
            int cont = 0;
            
            while (reader.Read()) {
                RE_GenericBean r = new RE_GenericBean();
                cont++;
                if (!reader.IsDBNull(0)) r.intC1 = reader.GetInt32(0); //employee
                if (!reader.IsDBNull(1)) r.strC1 = reader.GetString(1); //name
                if (!reader.IsDBNull(2)) r.decC1 = reader.GetDecimal(2); //salario
                if (!reader.IsDBNull(3)) r.decC2 = reader.GetDecimal(3); //bonificacion
                if (!reader.IsDBNull(4)) r.decC3 = reader.GetDecimal(4); //severeance
                if (!reader.IsDBNull(5)) r.decC4 = reader.GetDecimal(5); //severance bonus
                if (!reader.IsDBNull(6)) r.decC5 = reader.GetDecimal(6); //igss
                if (!reader.IsDBNull(7)) r.intC2 = reader.GetInt32(7); //cueneta
                if (!reader.IsDBNull(8)) r.intC3 = reader.GetInt32(8); //compania
                if (!reader.IsDBNull(9)) r.decC12 = reader.GetDecimal(9); //sev agreement
                
                      if (!reader.IsDBNull(10)) r.decC6 = reader.GetDecimal(10); //dayswork
                      if (!reader.IsDBNull(11)) r.decC7 = reader.GetDecimal(11); //extraorder
                      if (!reader.IsDBNull(12)) r.decC8 = reader.GetDecimal(12); //extrahours
                      if (!reader.IsDBNull(13)) r.decC9 = reader.GetDecimal(13); //adjustmentpos 
                      if (!reader.IsDBNull(14)) r.decC10 = reader.GetDecimal(14); //adjustmentneg
                      if (!reader.IsDBNull(15)) r.intC3 = reader.GetInt32(15); //igss flag
                      if (!reader.IsDBNull(16)) r.decC11 = reader.GetDecimal(16); //parking flag
                      if (!reader.IsDBNull(17)) r.intC5 = reader.GetInt32(17); //severance flag
                      if (!reader.IsDBNull(18)) r.decC13 = reader.GetDecimal(18); //cost extrahour
                      if (!reader.IsDBNull(19)) r.decC14 = reader.GetDecimal(19); //cost extraorder
                      if (!reader.IsDBNull(20)) r.decC15 = reader.GetDecimal(20); //extrahours2 (1.5)
                      if (!reader.IsDBNull(21)) r.strC6 = reader.GetString(21); //comments
                      if (!reader.IsDBNull(22)) r.intC6 = reader.GetInt32(22); //
                  

                  
                 
                  Decimal salarytotal = (r.decC1 + r.decC2) / 2;
                  Decimal dailyrate = salarytotal / 15;
                  Decimal basesalary = Decimal.Round(r.decC1/2,2);
                  Decimal ordinarysalary = r.decC1 / 2;
                  Decimal costhour = dailyrate / 8;
                  Decimal costorder = r.decC14;
                 
                  Decimal coefcosthour = Decimal.Round(r.decC13, 2); 
                  Decimal t1 = r.decC6 * (salarytotal / 15);
                  Decimal tt1= r.decC6 * (((r.decC1 + r.decC2) / 2)/15);
                  Decimal horas2 = r.decC15 * Decimal.Parse("1.5");
                  Decimal payhours = Decimal.Round((r.decC8+horas2 )* costhour, 2);
                  Decimal payorders = r.decC7 * costorder;
                 
                  Decimal t2 = t1 + Decimal.Round(payhours,2) +Decimal.Round(payorders,2) + r.decC9;
                  Decimal tt2 = tt1+ ((r.decC8+horas2 )* costhour)+(r.decC7 * costorder)+ r.decC9;
                  Decimal t2_fixed = Decimal.Round(t2, 2);  
                  Decimal igsst =  ((r.decC5/2) * Decimal.Parse("0.0483")) * r.intC3;
                  Decimal igsst_fixed = Decimal.Round(igsst, 2);
                  Decimal igssp = ((r.decC5 / 2) * Decimal.Parse("0.1267") ) * r.intC3;
                  Decimal igssp_fixed = Decimal.Round(igssp, 2);  
                  Decimal igsstt = igsst+igssp;
                  Decimal igsstt_fixed = Decimal.Round(igsstt, 2);
                  Decimal parking =  r.decC11;
                  Decimal parking_fixed = Decimal.Round(r.decC11, 2);
                  Decimal t3 = r.decC10 + igsst + parking;
                  
                  Decimal t3_fixed = Decimal.Round(t3, 2);
                  Decimal checkamount = t2_fixed - t3_fixed;
                  Decimal checkamount_fixed =  Decimal.Round(checkamount, 3);
                  Decimal check2 = Decimal.Round(tt2 - t3,2);
                  

                  Decimal severancebonus = r.decC4/2;
                  Decimal severancebonus_fixed = Decimal.Round(r.decC4/2, 2);
                  Decimal severancetotal = (r.decC3 + r.decC4)/2;
                  Decimal severancetotal_fixed = Decimal.Round(severancetotal/2, 2);
                  Decimal bono13 = (severancetotal/12)*r.intC5;
                  Decimal bono13_fixed = Decimal.Round(bono13, 2);
                  sumabono13 += bono13_fixed; 
                  Decimal bono15=0;
                    if (r.decC12.Equals(0))
                    {
                        bono15 = ((r.decC3 / 2) / 12) * r.intC5;
                    }
                    else {
                        bono15 = ((r.decC12 / 2) / 12) * r.intC5;
                    }
                  Decimal bono15_fixed = Decimal.Round(bono15,2);
                  sumabono15 += bono15_fixed;
                  Decimal totalbonus = bono13 *2 + bono15;
                  Decimal totalbonus_fixed = Decimal.Round(totalbonus, 2);
                  //Decimal grantotal = checkamount + igsstt + totalbonus;
                  Decimal grantotal = check2 + igsstt + totalbonus;
                  Decimal grantotal_fixed =  Decimal.Round(grantotal, 2);
                  sumaigsst+= igsst;
                  sumaigssp+= igssp;
                  sumaigsstt+= igsstt;
                  //sumachek+= checkamount_fixed;
                  sumachek += check2;
                  sumatotalbonus+= totalbonus_fixed;
                  sumagrantotal += grantotal_fixed;


                  object[] obj = { cont, r.intC1, r.strC1, r.strC3, String.Format("{0:0.00}",salarytotal), String.Format("{0:0.00}",r.decC2 / 2), String.Format("{0:0.00}",igsst_fixed), String.Format("{0:0.00}",igssp_fixed), String.Format("{0:0.00}",igsstt_fixed), String.Format("{0:0.00}",r.decC5 / 2), r.strC4, r.strC5, String.Format("{0:0.00}",r.decC6), String.Format("{0:0.00}",r.decC7), String.Format("{0:0.00}",r.decC8), String.Format("{0:0.00}",r.decC9), String.Format("{0:0.00}",r.decC10), String.Format("{0:0.00}",parking_fixed), r.intC3, r.intC5, String.Format("{0:0.00}",dailyrate), String.Format("{0:0.00}",Decimal.Round(t1, 2)), String.Format("{0:0.00}",Decimal.Round(basesalary, 2)), Decimal.Round(ordinarysalary, 2), Decimal.Round(costhour, 2), Decimal.Round(costorder, 2), String.Format("{0:0.00}", payhours), Decimal.Round(payorders, 2), String.Format("{0:0.00}",t2_fixed), String.Format("{0:0.00}",t3_fixed), string.Format(CultureInfo.CreateSpecificCulture("es-gt"), "{0:c}",check2), String.Format("{0:0.00}",r.decC3 / 2), String.Format("{0:0.00}",severancebonus_fixed), String.Format("{0:0.00}",severancetotal_fixed), String.Format("{0:0.00}", bono13_fixed), String.Format("{0:0.00}", bono13_fixed), String.Format("{0:0.00}", bono15_fixed), String.Format("{0:0.00}", totalbonus_fixed), string.Format(CultureInfo.CreateSpecificCulture("es-gt"), "{0:c}", grantotal_fixed), Decimal.Round(coefcosthour, 2), r.decC12 / 2, Decimal.Round(r.decC15, 2), r.strC6, r.intC6};

                ds.Tables[0].Rows.Add(obj);
            
            
            
            
            }
            sumaigssp = Decimal.Round(sumaigssp, 2);
            sumaigsst = Decimal.Round(sumaigsst, 2);
            sumaigsstt = Decimal.Round(sumaigsstt, 2);
            sumachek = Decimal.Round(sumachek,2);
            sumagrantotal = Decimal.Round(sumagrantotal, 2);
            sumatotalbonus = Decimal.Round(sumatotalbonus, 2);
            sumabono13 = Decimal.Round(sumabono13, 2);
            sumabono15 = Decimal.Round(sumabono15, 2);
            object[] obj2 = { "", "", "Totals", "", "", "", sumaigsst, sumaigssp, sumaigsstt, "", "", "", "", "", "", "", "", "", 0, 0, "", "", "", "", "", "", "", "", "", "", string.Format(CultureInfo.CreateSpecificCulture("es-gt"), "{0:c}", sumachek), "", "", "", sumabono13, sumabono13, sumabono15, sumatotalbonus, string.Format(CultureInfo.CreateSpecificCulture("es-gt"), "{0:c}", sumagrantotal), "","","","",0 };

            ds.Tables[0].Rows.Add(obj2);
        } catch(Exception ex){
            utility.log(ex.Message.ToString()+ex.StackTrace);
            return null;
        
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return ds;
    
    }

    public static int existEmployeeCode(string code, int empid)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select emp_id  from tbl_empleado where emp_codigo_empleado like @id and  emp_id<>@empid"; //toma los valores de la tabla donde el id sea igual al de el seleccionado
            comm.Parameters.Add("@id", SqlDbType.Int).Value = code;
            comm.Parameters.Add("@empid", SqlDbType.Int).Value = empid;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) result = reader.GetInt32(0); //id
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    
    }

    public static int updatePayrollDetail(ArrayList relist)
    {
        // Agrega o edita los permisos de los documentos de compañia
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            foreach (RE_GenericBean rg in relist)
            {
                comm.CommandText = " MERGE tbl_payroll_detail ";
                comm.CommandText += " USING (SELECT @payrollid AS ID1, @empid AS ID2) AS EX ON (EX.ID1 = tbl_payroll_detail.payroll_id AND EX.ID2 = tbl_payroll_detail.emp_id) ";
                comm.CommandText += " WHEN MATCHED THEN ";
                comm.CommandText += " UPDATE SET dayswork=@dayswork, extraorders=@extraorders, extrahours=@extrahours,extrahours2=@extrahours2 ,adjustmentpos=@adjustmentpost, adjustmentneg=@adjustmentneg, igss=@igss, severance=@severance, parking=@parking, comment=@comment, bank=@bank ";
                comm.CommandText += " WHEN NOT MATCHED THEN ";
                comm.CommandText += " INSERT (payroll_id, emp_id, dayswork, extraorders, extrahours, adjustmentpos, adjustmentneg, igss, severance, parking,cost_orderext,extrahours2, bank) VALUES (@payrollid, @empid, @dayswork, @extraorders, @extrahours, @adjustmentpost, @adjustmentneg, @igss,@severance, @parking,@cost_orderext,@extrahours2, @bank); ";
                comm.Parameters.Add("@payrollid", SqlDbType.Int).Value = rg.intC1;
                comm.Parameters.Add("@empid", SqlDbType.Int).Value = rg.intC2;
                comm.Parameters.Add("@dayswork", SqlDbType.Decimal).Value = rg.decC2;
                comm.Parameters.Add("@extraorders", SqlDbType.Decimal).Value = rg.decC4;
                comm.Parameters.Add("@extrahours2", SqlDbType.Decimal).Value = rg.decC13;
                comm.Parameters.Add("@extrahours", SqlDbType.Decimal).Value = rg.decC3;
                comm.Parameters.Add("@adjustmentpost", SqlDbType.Decimal).Value = rg.decC5;
                comm.Parameters.Add("@adjustmentneg", SqlDbType.Decimal).Value = rg.decC6;
                comm.Parameters.Add("@igss", SqlDbType.Decimal).Value = rg.decC7;
                comm.Parameters.Add("@severance", SqlDbType.VarChar).Value = rg.decC8;
                comm.Parameters.Add("@parking", SqlDbType.Decimal).Value = rg.decC9;
                comm.Parameters.Add("@cost_orderext", SqlDbType.Decimal).Value = rg.decC11;
                comm.Parameters.Add("@comment", SqlDbType.VarChar).Value = rg.strC1;
                comm.Parameters.Add("@bank", SqlDbType.Int).Value = rg.intC6;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            result = 1;

        }
        catch (Exception e)
        {

            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
        
    }

    public static ArrayList getSettings(string where)
    {
        ArrayList result = new ArrayList();
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "select id, name, value, description from tbl_settings " + where;
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0); //id 
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);//name
                if (!reader.IsDBNull(2)) rgb.decC1 = reader.GetDecimal(2);//value
                if (!reader.IsDBNull(3)) rgb.strC2 = reader.GetString(3);//descripcion
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            utility.log(e.Message.ToString());
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
        
    }

    public static int updateCommentPayrollDetail(RE_GenericBean r)
    {
        // Agrega o edita los permisos de los documentos de compañia
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        try
        {
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

                comm.CommandText = " UPDATE tbl_payroll_detail SET comment=@comment where payroll_id=@payrollid and emp_id=@empid";
                comm.Parameters.Add("@payrollid", SqlDbType.Int).Value = r.intC1;
                comm.Parameters.Add("@empid", SqlDbType.Int).Value = r.intC2;
                comm.Parameters.Add("@comment", SqlDbType.VarChar).Value = r.strC1;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
           
            result = 1;

        }
        catch (Exception e)
        {

            utility.log(e.Message.ToString());
            return 0;
        }
        finally
        {
            if (reader != null) reader.Dispose();
            if (comm != null) comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    
    public static ds_payroll getPayrollDetailDS(int payid, string where)
    {
        ds_payroll ds = new ds_payroll();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        SqlConnection conn2 = null;
        SqlCommand comm2 = null;
        SqlDataReader reader2 = null;
        try
        {
            conn = OpenConnection();
            conn2 = OpenConnection();
            comm = new SqlCommand();
            comm2 = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm2.Connection = conn2;
            comm2.CommandType = CommandType.Text;

            Decimal sumaigssp = 0;
            Decimal sumaigsst = 0;
            Decimal sumaigsstt = 0;
            Decimal sumachek = 0;
            Decimal sumatotalbonus = 0;
            Decimal sumagrantotal = 0;

            comm.CommandText = " select convert(varchar,startdate), convert(varchar,enddate), completed from tbl_payroll where id =@id ";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = payid;
            reader = comm.ExecuteReader();
            string planinicio = "1/1/2900";
            string planfin = "1/1/1900";
            while (reader.Read())
            {
                if (!reader.IsDBNull(0)) planinicio = reader.GetString(0); //fecha incuicio planilla
                if (!reader.IsDBNull(1)) planfin = reader.GetString(1); //fecha fin planilla

            }
            reader.Close();
            comm.Parameters.Clear();


            comm.CommandText = "SELECT     tbl_payroll_detail.emp_id, tbl_persona.tper_nombre+' '+tbl_persona.tper_apellido, tbl_payroll_detail.salary, tbl_payroll_detail.bonus, tbl_payroll_detail.sevvalue, tbl_payroll_detail.sevbonus, tbl_payroll_detail.igssvalue, tbl_empleado.emp_tcu_id, tbl_cuenta.tcu_tco_id, ";
            comm.CommandText += " tbl_payroll_detail.sevagreement, tbl_payroll_detail.dayswork, tbl_payroll_detail.extraorders, tbl_payroll_detail.extrahours, tbl_payroll_detail.adjustmentpos, tbl_payroll_detail.adjustmentneg, tbl_payroll_detail.igss, tbl_payroll_detail.parking, tbl_payroll_detail.severance, ";
            comm.CommandText += " tbl_payroll_detail.coef_hourext, tbl_payroll_detail.cost_orderext,tbl_payroll_detail.extrahours2, tbl_payroll_detail.comment, tbl_puesto_empleado.tpe_nombre FROM         tbl_payroll_detail INNER JOIN tbl_empleado ON tbl_payroll_detail.emp_id = tbl_empleado.emp_id INNER JOIN tbl_persona ON tbl_empleado.emp_tper_id = tbl_persona.tper_id INNER JOIN ";
            comm.CommandText += " tbl_cuenta ON tbl_empleado.emp_tcu_id = tbl_cuenta.tcu_id INNER JOIN tbl_expediente ON tbl_empleado.emp_id = tbl_expediente.tex_emp_id INNER JOIN tbl_puesto_empleado ON tbl_expediente.tex_tpe_id = tbl_puesto_empleado.tpe_id ";
            comm.CommandText += " WHERE tbl_payroll_detail.payroll_id=@id ORDER BY tbl_persona.tper_nombre  ";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = payid;




            reader = comm.ExecuteReader();
            int cont = 0;

            while (reader.Read())
            {
                RE_GenericBean r = new RE_GenericBean();
                cont++;
                if (!reader.IsDBNull(0)) r.intC1 = reader.GetInt32(0); //employee
                if (!reader.IsDBNull(1)) r.strC1 = reader.GetString(1); //name
                if (!reader.IsDBNull(2)) r.decC1 = reader.GetDecimal(2); //salario
                if (!reader.IsDBNull(3)) r.decC2 = reader.GetDecimal(3); //bonificacion
                if (!reader.IsDBNull(4)) r.decC3 = reader.GetDecimal(4); //severeance
                if (!reader.IsDBNull(5)) r.decC4 = reader.GetDecimal(5); //severance bonus
                if (!reader.IsDBNull(6)) r.decC5 = reader.GetDecimal(6); //igss
                if (!reader.IsDBNull(7)) r.intC2 = reader.GetInt32(7); //cueneta
                if (!reader.IsDBNull(8)) r.intC3 = reader.GetInt32(8); //compania
                if (!reader.IsDBNull(9)) r.decC12 = reader.GetDecimal(9); //sev agreement

                if (!reader.IsDBNull(10)) r.decC6 = reader.GetDecimal(10); //dayswork
                if (!reader.IsDBNull(11)) r.decC7 = reader.GetDecimal(11); //extraorder
                if (!reader.IsDBNull(12)) r.decC8 = reader.GetDecimal(12); //extrahours
                if (!reader.IsDBNull(13)) r.decC9 = reader.GetDecimal(13); //adjustmentpos 
                if (!reader.IsDBNull(14)) r.decC10 = reader.GetDecimal(14); //adjustmentneg
                if (!reader.IsDBNull(15)) r.intC3 = reader.GetInt32(15); //igss flag
                if (!reader.IsDBNull(16)) r.decC11 = reader.GetDecimal(16); //parking flag
                if (!reader.IsDBNull(17)) r.intC5 = reader.GetInt32(17); //severance flag
                if (!reader.IsDBNull(18)) r.decC13 = reader.GetDecimal(18); //cost extrahour
                if (!reader.IsDBNull(19)) r.decC14 = reader.GetDecimal(19); //cost extraorder
                if (!reader.IsDBNull(20)) r.decC15 = reader.GetDecimal(20); //extrahours2 (1.5)
                if (!reader.IsDBNull(21)) r.strC6 = reader.GetString(21); //comments
                if (!reader.IsDBNull(22)) r.strC7 = reader.GetString(22); //puesto




                Decimal salarytotal = (r.decC1 + r.decC2) / 2;
                Decimal dailyrate = salarytotal / 15;
                Decimal basesalary = Decimal.Round(r.decC1 / 2, 2);
                Decimal ordinarysalary = r.decC1 / 2;
                Decimal costhour = dailyrate / 8;
                Decimal costorder = r.decC14;

                Decimal coefcosthour = Decimal.Round(r.decC13, 2);
                Decimal t1 = r.decC6 * (salarytotal / 15);
                Decimal horas2 = r.decC15 * Decimal.Parse("1.5");
                Decimal payhours = Decimal.Round((r.decC8 + horas2) * costhour, 2);
                Decimal payorders = r.decC7 * costorder;

                Decimal t2 = t1 + payhours + payorders + r.decC9;
                Decimal t2_fixed = Decimal.Round(t2, 2);
                Decimal igsst = ((r.decC5 / 2) * Decimal.Parse("0.0483")) * r.intC3;
                Decimal igsst_fixed = Decimal.Round(igsst, 2);
                Decimal igssp = ((r.decC5 / 2) * Decimal.Parse("0.1267")) * r.intC3;
                Decimal igssp_fixed = Decimal.Round(igssp, 2);
                Decimal igsstt = igsst + igssp;
                Decimal igsstt_fixed = Decimal.Round(igsstt, 2);
                Decimal parking = r.decC11;
                Decimal parking_fixed = Decimal.Round(r.decC11, 2);
                Decimal t3 = r.decC10 + igsst + parking;
                Decimal t3_fixed = Decimal.Round(t3, 2);
                Decimal checkamount = t2 - t3;
                Decimal checkamount_fixed = Decimal.Round(checkamount, 2);

                Decimal severancebonus = r.decC4 / 2;
                Decimal severancebonus_fixed = Decimal.Round(r.decC4 / 2, 2);
                Decimal severancetotal = (r.decC3 + r.decC4) / 2;
                Decimal severancetotal_fixed = Decimal.Round(severancetotal / 2, 2);
                Decimal bono13 = (severancetotal / 12) * r.intC5;
                Decimal bono13_fixed = Decimal.Round(bono13, 2);
                Decimal bono15 = 0;
                Decimal sevcalcu = 0;
                if (r.decC12.Equals(0))
                {
                    bono15 = ((r.decC3 / 2) / 12) * r.intC5;
                    sevcalcu = (r.decC3 / 2) ;
                }
                else
                {
                    bono15 = ((r.decC12 / 2) / 12) * r.intC5;
                    sevcalcu = (r.decC12 / 2);
                }

                Decimal bono15_fixed = Decimal.Round(bono15, 2);
                Decimal totalbonus = bono13 * 2 + bono15;
                Decimal totalbonus_fixed = Decimal.Round(totalbonus, 2);
                Decimal grantotal = checkamount + igsstt + totalbonus;
                Decimal grantotal_fixed = Decimal.Round(grantotal, 2);
                sumaigsst += igsst;
                sumaigssp += igssp;
                sumaigsstt += igsstt;
                sumachek += checkamount;
                sumatotalbonus += totalbonus;
                sumagrantotal += grantotal;


                //object[] obj = { cont, r.intC1, r.strC1, r.strC3, salarytotal, r.decC2 / 2, igsst_fixed, igssp_fixed, igsstt_fixed, r.decC5 / 2, r.strC4, r.strC5, r.decC6, r.decC7, r.decC8, r.decC9, r.decC10, parking_fixed, r.intC3, r.intC5, dailyrate, Decimal.Round(t1, 2), basesalary, ordinarysalary, costhour, costorder, payhours, Decimal.Round(payorders, 2), t2_fixed, t3_fixed, string.Format(CultureInfo.CreateSpecificCulture("es-gt"), "{0:c}", checkamount), r.decC3 / 2, severancebonus_fixed, severancetotal_fixed, bono13_fixed, bono13_fixed, bono15_fixed, totalbonus_fixed, string.Format(CultureInfo.CreateSpecificCulture("es-gt"), "{0:c}", grantotal_fixed), Decimal.Round(coefcosthour, 2), r.decC12 / 2, Decimal.Round(r.decC15, 2), r.strC6 };
                ds.datos.AdddatosRow(r.decC9.ToString(), r.intC1.ToString(), r.strC1 + " , " + r.strC7, r.intC3.ToString(), r.intC5.ToString(), r.decC1.ToString(), r.decC6.ToString(), Decimal.Round(t1, 2).ToString(), ordinarysalary.ToString(), (r.decC2 / 2).ToString(), r.decC8.ToString(), r.decC15.ToString(), payhours.ToString(), r.decC7.ToString(), Decimal.Round(payorders, 2).ToString(), t2_fixed.ToString(), r.decC10.ToString(), igsst_fixed.ToString(), igssp_fixed.ToString(), igsstt_fixed.ToString(), parking.ToString(), t3_fixed.ToString(), checkamount_fixed.ToString(), totalbonus_fixed.ToString(), grantotal_fixed.ToString(), String.Format("{0:0.00}", Decimal.Round(dailyrate, 2)), costhour.ToString(), costorder.ToString(), bono13_fixed.ToString(), bono13_fixed.ToString(), bono15_fixed.ToString(), Decimal.Round(r.decC5 / 2, 2).ToString(), sevcalcu.ToString(), String.Format("{0:0.00}", Decimal.Round(r.decC3/2, 2)), String.Format("{0:0.00}", Decimal.Round(r.decC4/2, 2)), "");
                //ds.Tables[0].Rows.Add(obj);




            }
           
        }
        catch (Exception ex)
        {
            utility.log(ex.Message.ToString() + ex.StackTrace);
            return null;

        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return ds;

    }

    public static DataSet readArt()
    {
        DataSet art;
        try
        {
            OdbcConnection con = new OdbcConnection();
            String cadena = "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:/FactuSol 2000/Datos/0012012.mdb;Uid=;Pwd=;";
            con.ConnectionString = cadena;
            //String consulta = " SELECT CODART, EANART, FAMART, DESART, PCOART, PHAART, FTEART  FROM  F_ART ";
            String consulta = " SELECT F_ART.CODART as Codigo, F_ART.EANART as Cod_Barras, F_ART.DESART as Descripcion, F_ART.PCOART as Precio, F_ART.PHAART as Proveedor, ";
            consulta += " F_ART.FTEART as Fabricante, F_ART.FAMART as Familia, F_FAM.DESFAM as Des_Familia, F_FAM.SECFAM as Seccion ";
            consulta += " FROM  F_ART INNER JOIN F_FAM ON F_ART.FAMART = F_FAM.CODFAM ";
            OdbcDataAdapter adaptador = new OdbcDataAdapter(consulta, con);
            art = new DataSet();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            adaptador.Fill(art);
            return art;
        }
        catch (Exception ex)
        {
            utility.log("Error al leer bases de datos de access");
            return null;
        }
    }
     
}

